Vlookup and VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Howdy all
I've read all of the stuff on vlookup here and everything seems to point to using it in a worksheeet cell. Can the vlookup function be used in VBA and assign its output to a variable? If so how? I have tried the Application.worksheetfunction.vblookup() and various other forms but get nothing but errors
Any help would be appreciated

Victor
 
Option Explicit
Sub testme()

Dim res As Variant
Dim myCell As Range
Dim myRng As Range

With Worksheets("sheet2")
Set myRng = .Range("a1:e99")
End With

Set myCell = ActiveCell

res = Application.VLookup(myCell.Value, myRng, 3, False)

If IsError(res) Then
MsgBox "it's an error"
Else
MsgBox "it's: " & res
End If
End Sub

should work ok.
 
Back
Top