Welcome to the new platform of Programmers Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use it's exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.
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:
.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!