Run time error 438

[Keem2019]

Complete novice and am trying to get the below code working:



Sub GVLOOKUP()
'
' VLOOKUP Macro
'


'
Cells.Select
Cells.EntireColumn.AutoFit
Range("E1").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "Description"
Range("E2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.Formula2R1C1 = _
"=VLOOKUP(RC[-4]:R[10507]C[-4],Sheet3!R[-1]C[-4]:R[10636]C[-3],2,FALSE)"
Columns("E:E").EntireColumn.AutoFit

End Sub

Works fine on Excel 2016, but when I try and use in Excel 2010 I get the Run-time error 438.
Any ideas please?

 

[Yongle]

Your formula looks wrong to me. I would expect a single cell lookup not a range as the first argument in the formula
"=VLOOKUP(RC[-4]:R[10507]C[-4],Sheet3!R[-1]C[-4]:R[10636]C[-3],2,FALSE)"

I amended it to this
"=VLOOKUP(RC[-4],Sheet3!R[-1]C[-4]:R[10636]C[-3],2,FALSE)"

which places this formula in the cell
=VLOOKUP(A2,Sheet3!A1:B10638,2,FALSE)

You could use this
"=VLOOKUP(RC[-4],Sheet3!C[-4]:C[-3],2,FALSE)"

for this formula
=VLOOKUP(A2,Sheet3!A:B,2,FALSE)


Your code is amended below to remove all selections (usually not required in VBA) and cell references are qualified with a SHEET reference

Sub GVLOOKUP()
    With ActiveSheet
        .Cells.EntireColumn.AutoFit
        With .Range("E1")
            .ClearContents
            .FormulaR1C1 = "Description"
        End With
        .Range("E2").FormulaR1C1 = "=VLOOKUP(RC[-4],Sheet3!R[-1]C[-4]:R[10636]C[-3],2,FALSE)"
        .Columns("E:E").EntireColumn.AutoFit
    End With
End Sub

 

[Keem2019]

Thanks so much!!!
Works good but doesn't auto fill the remainder of the column with VLOOKUP, only does it in the first row.
Any ideas on how to apply to entire column?