Auofill a formula down to the last row of data in Excel

I have the following code which will autofill my VLOOKUP statement when there is more than one record in my spreadsheet however, when there is only 1 row of data the code errors out: "Autofill Method of Range Class Failed"

Here is my code:

[italic][color=Blue]With Range("F2")
.FormulaR1C1 = "=VLOOKUP(RC[-1], '[MyFileName.xlsx]MySheetName'!R1C1:R73C4, 4, TRUE)"
.AutoFill Destination:=Range("F2", Cells(Cells(Rows.Count, 7).End(xlUp).Row, 6))
End With[/color][/italic]

The line which begins .FormulaR1C1 completes as desired however, the line which begins .AutoFill Destination is where the code errors.

When I go to Debug and hover my mouse over the Rows.Count in the second line it reads 65536 and when I hover my mouse over the .End(xlUp).Row it reads -4162.

Any guidance which someone could offer would be greatly appreciated! I was informed that I may need to add a handler which I am not familiar enough with writing code to know how to do this however, I am willing to give it a try!
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


In this Discussion