Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2142
  • Last Modified:

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!
0
BBRRGG
Asked:
BBRRGG
3 Solutions
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now