Runtime error when autofill of empty cells

Hi,

I have a macro that does a VLOOKUP and then autofills the results to a specified column. However, if there are zero results from the VLOOKUP it fails with a Runtime error. Do you know if there is any way around this?

The code is below:

Range("D1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-2],Sheet2!C[-2]:C[-1],2,FALSE)"
Selection.AutoFill Destination:=Range("D1:D" & lastrow)

Thanks

Hi

Is the problem with not defining your last row. The code below worked OK.

Regards

Dominic

Sub Macro1()
    lastrow = Range("B1").CurrentRegion.Rows.Count
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-2],Sheet2!C[-2]:C[-1],2,FALSE)"
    Selection.AutoFill Destination:=Range("D1:D" & lastrow)
End Sub

Another:

Range("D1").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-2],Sheet2!C[-2]:C[-1],2,FALSE)"
Selection.AutoFill Destination:=Range("D1:D" & Range("D" & rows.count).End(3)(1).row)

Sorry, I should have said, I was defining "lastrow" as lastrow = Cells(Rows.Count, "A").End(xlUp).Row.
I've tried both suggestions, but I still get the same Runtime error.

Any other suggestions?

No this doesn't work. It doesn't copy down, apart from the 1st row.

Sorry, not sure what you mean?

Hi

I still think the problem with your original code is the calculation of lastrow. I suggest you try putting a message box into your original code that displays the value of lastrow before you select D1 and get to the Autofill.

Regards

Dominic