vba-excel-training - Autofill in Excel using ranges defined by variables

Resolved · Low Priority · Version Standard

Thu 11 Oct 2007, 09:20 Delegate 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:31 Trainer 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:41 Delegate 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:52 Trainer 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:10 Delegate 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:32 Trainer Carlos said...