I am doing a VLOOKUP and only want to fill to a certain range - The number of blank rows appearing in column "d" as the number of rows will always vary. I have tried the following code and the vlookup places the first value in cell e2, but it stops at the Selection.Autofill line. I am getting a Run-time error Autofill method of Range class failed. Here is my code. I am using the t to count the number of rows.
Sub test_autofill() Dim t As Long t = Cells(Rows.Count, "d").End(xlUp).Row Range("e2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],catcode.xls!CVA_PERCENT,2,0)" Selection.AutoFill Destination:=Range("e2" & t), Type:=xlFillDefault Range("e2" & t).Select MsgBox "please remove OT services for all customers except EQP & GSA" End Sub