how to do a vlookup in vba?

[kjy1989]

Hi guys,

how do you execute a vlookup in vba? What i normally do is turn on the macro recorder and type the vlookup function then that's it. But my concern is that i dont want the formula to be seen when the macro is executed and people can see a glimpse of the formula if i use the macro recorder to perform a vlookup function. so for example:

 Sub testingggg()
'
' Macro2 Macro
'

   
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'C:\Users\Kevin\Desktop\FINAL preliminary release of estim program\[MASTER PRICELIST.xlsm]PRICELIST'!R1C1:R65536C43,2,FALSE)"
    ActiveCell.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

how do you change that Activecell.formulaR1C1 line and make it into a legitimate vba code that when the user performs the macro it will be smooth and they wont catch a glimpse of the formula?

[TMS]

 Sub testingggg()
'
' Macro2 Macro
' 
Application.ScreenUpdating=False
With ActiveCell.Offset(0, 1)
    .FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'C:\Users\Kevin\Desktop\FINAL preliminary release of estim program\[MASTER PRICELIST.xlsm]PRICELIST'!R1C1:R65536C43,2,FALSE)"
    .Value = .Value
End With
Application.ScreenUpdating=True
End Sub

Regards, TMS

[kjy1989]

No way, that simple???? will try it out and give you a star if it works.

[TMS]

Yes,way. That simple. Thanks for the rep.