VBA: using VLOOKUP in VBA and inserting result into an Excel cell

I'm using VLOOKUP in VBA to populate a cell in Excel.  Currently I have the following code:

Function VLookupFunc()
Set rng = Sheets("Sheet1").Range("H3:I33")
With Application
Debug.Print .VLookup(constant, rng, 2, False)

I'm getting the correct value printed out in my VBA editor.  How do I get this value into the Excel cell?  Maybe I don't have to use 'With application' to accomplish this?

Thanks!
BBRRGGAsked:
Who is Participating?
 
Ardhendu SarangiSr. Project ManagerCommented:
Hi,
If you want to insert this value into a cell then you can use the following:

 Range("A1") = Application.WorksheetFunction.VLookup(constant, rng, 2, False)

Let me know if this doesn't work.

Ardhendu
0
 
patrickabCommented:
BBRRGG,
Use a function as below. It's in the attached file.
Patrick

Function VLF(ByVal myval As Range) As Variant
Dim rng As Range
Set rng = Sheets("Sheet1").Range("H3:I33")
VLF = WorksheetFunction.VLookup(myval, rng, 2, False)
End Function

Open in new window

BBRRGG-01.xls
0
 
ducky801Commented:
Activesheet.Range("A1").value  = Application.WorksheetFunction.VLookup(constant, rng, 2, False)  
should do the trick for you
 
Hope this helps
 
AR

 
0
 
BBRRGGAuthor Commented:
Thanks !
0
 
patrickabCommented:
BBRRGG - Thanks for the points - Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.