98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » vba-excel-training - Autofill in Excel using ranges defined by variables
vba-excel-training - Autofill in Excel using ranges defined by variables
Resolved · Low Priority · Version Standard
Thu 11 Oct 2007, 09:20Delegate Jane said...
Jane has attended:
Excel VBA Intro Intermediate course
Autofill in Excel using ranges defined by variables
I'm trying to get the last column to autofill across in order for the month headers to be updated correctly. I have the row number and column number parsed ito variables. This is the basic code, but I can't get it to accept the ranges defined by the variables (only seems to work if the range is hard-coded).
Cells(RowCount, ColumnCount).Select
Selection.AutoFill Destination:=Range(Cells(RowCount, ColumnCount), Cells(RowCount, ColumnCount + 1)), Type:=xlFillMonths
Any help please?
thanks,
Jane
Wed 24 Oct 2007, 10:31Trainer Carlos said...
RE: Autofill in Excel using ranges defined by variables
Hi Jane
The xlFillMonths only works if the month is written in the first cell of the autofill. The system needs to know on which month to start.
I reset your code below and it did the autifill perfectly. Note the last ColumnCount is + 11 so as to Autofill 12 cells.
Cells(RowCount, ColumnCount).Value = "January"
Selection.AutoFill Destination:=Range(Cells(RowCount, ColumnCount), Cells(RowCount, ColumnCount + 11)), Type:=xlFillMonths
Hope this helps
Carlos
Wed 24 Oct 2007, 10:41Delegate Jane said...
RE: Autofill in Excel using ranges defined by variables
Hi Carlos,
Thanks for that, but the month was already in the first cell of the autofill area as 01/09/2007. It appears to be the second row that the debugger is getting stuck on, not the first.
Cheers,
Jane
Wed 24 Oct 2007, 10:52Trainer Carlos said...
RE: Autofill in Excel using ranges defined by variables
Jane
I just tested the code I gave you with the date 01/09/2007 and it gave me a Autofill of 12 monthly dates to 01/08/2008
I entered the date in "A1". Then ran the following code:
Cells(RowCount, ColumnCount).Select
Selection.AutoFill Destination:=Range(Cells(RowCount, ColumnCount), Cells(RowCount, ColumnCount + 11)), Type:=xlFillMonths
Carlos
Wed 24 Oct 2007, 11:10Delegate Jane said...
RE: Autofill in Excel using ranges defined by variables
Thanks Carlos, that's identical to the code I originally posted up, so the problem must actually be elsewhere, maybe where I'm finding the values to store in the variables. I'll check through the rest of my code some more.
Cheers,
Jane
Wed 24 Oct 2007, 12:32Trainer Carlos said...
RE: Autofill in Excel using ranges defined by variables
Jane
Hope you find where the problem is occurring.
Carlos
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Shortcut fill a cell with contents from adjacent cellsUse Control + D to fill a cell with the data from an adjacent cell. This speeds up data entry and is a cool tip to share! |
