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

steelervince

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!

tomrabbit

I hope you can view here for information about the VBA:
[b][u][link=http://www.e-iceblue.com/Knowledgebase/Spire.XLS/Demos/VBA.html]http://www.e-iceblue.com/Knowledgebase/Spire.XLS/Demos/VBA.html[/link][/u][/b]
Information about formula:
[b][u][link=http://www.e-iceblue.com/Knowledgebase/Spire.XLS/Demos/Formulas.html]http://www.e-iceblue.com/Knowledgebase/Spire.XLS/Demos/Formulas.html[/link][/u][/b]