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:
.FormulaR1C1 = "=VLOOKUP(RC[-1], '[MyFileName.xlsx]MySheetName'!R1C1:R73C4, 4, TRUE)"
.AutoFill Destination:=Range("F2", Cells(Cells(Rows.Count, 7).End(xlUp).Row, 6))
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!