Keem2019

New Member
Joined
Sep 11, 2019
Messages
2
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?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
Code:
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
 
Upvote 0
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?
:)
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top