CODEWITHSUNDEEP
excelvba
Ryan Dunphy
Ryan Dunphy

Reputation: 848

Autofill Range using R1C1

I'm trying to autofill a formula using a dynamic range in Excel VBA using R1C1 referencing.

Rather than using something like this:

Sheets("my_sheet").Range("A2").AutoFill Destination:=Range("A2:A:"&row_count), Type:=xlFillDefault

I have written the following:

Sheets("my_sheet").Cells(2, 17).AutoFill Destination:=Sheets("my_sheet").Range(Cells(3, 17), Cells(row_count, 17)), Type:=xlFillDefault

This doesn't seem to work though and returns the following:

Run-time error '1004':
AutoFill method of Range class failed.

Any suggestions?

Upvotes: 1

Views: 2305

Answers (1)

user2946806
user2946806

Reputation:

From MSDN documentation "The destination must include the source range." The problem with your second equation is actually that the destination address starts below the row than the autofill reference. You reference row 2 but the destination starts on row 3. Change 3 to 2 and it will work.

Your second equation is fine but it's not equivalent to the first one.

If your first equation works you can directly replace it with this:

Sheets("my_sheet").Cells(2, 1).AutoFill Destination:=Sheets("my_sheet").Range(Cells(2, 1), Cells(row_count, 1)), Type:=xlFillDefault

BTW - your first equation has an extra ':' after A2:A:

It should read like this:

Sheets("my_sheet").Range("A2").AutoFill Destination:=Range("A2:A" & row_count), Type:=xlFillDefault

Upvotes: 2

Related Questions