Autofill a dynamic range that consist of two variables

[HJA14]

Dear all,

I have a cell range that changes continuously. Therefore, both cells (begin- and end cell) are variable and not static. I would like to make sure that the cells in this rang have the "same formula". This formula has A1 (not R1C1)-notation and consist of multiple cell references.

[/COLOR][SIZE=2]Range("C" & LastRowOld).Select
[/SIZE][/FONT][FONT=arial][SIZE=2]With Range("C" & LastRowOld)[/SIZE][/FONT]
[FONT=arial][SIZE=2]    .Formula = "myFormula"[/SIZE][/FONT]
[FONT=arial][SIZE=2]    .AutoFill Destination:=Range("C" & LastRowOld & ":C" & LastRowNew)[/SIZE]
[/FONT]

Earlier in my VBA-macro I declared the variables LastRowNew and LastRowOld. I get the following error message Autofill method of Range classes failed.

I know that if the start cell would have been C2 (static) the following macro would have done the job:

[/COLOR][SIZE=2]Range("C2").Select
[/SIZE][/FONT][FONT=arial][SIZE=2]With Range("C2")[/SIZE][/FONT]
[FONT=arial][SIZE=2]    .Formula = "myFormula"[/SIZE][/FONT]
[FONT=arial][SIZE=2]    .AutoFill Destination:=Range("C2 & C" & LastRowNew)[/SIZE]
[/FONT]

How do I write the .AutoFill Destination:=Range("C" & LastRowOld & ":C" & LastRowNew) correctly?

Thanks in advance,

 

[footoo]

Range("C" & LastRowOld & ":C" & LastRowNew) .Formula = "myFormula"

 

[HJA14]

Range("C" & LastRowOld & ":C" & LastRowNew) .Formula = "myFormula"

Thank you for taking the time to help me with my problem. The problem with your solution is that the formula in the cells are not based on R1C1-notation. For example the formula in cell "C" & LastRowOld is

.Formula = "=INDEX(Sheet1!D:D,MATCH(Data!B & LastRowOld,Sheet1!C:C,0))"

So, I need the macro to autofill this formula with the correct cell reference in the other cells of the range. Any suggestions?

 

[footoo]

I can't see why this shouldn't work :

Range("C" & LastRowOld & ":C" & LastRowNew) .Formula = "=INDEX(Sheet1!D:D,MATCH(Data!B" & LastRowOld & ",Sheet1!C:C,0))"