Use VLookup in vba

**mellisahi** · 11-09-2009

I am using i am using Visual basic and don't have much more information about VBA, in my form in need to use VLookup in VBA to find something in a list based on input in a form? I have try a lot to find over internet but was not able to get any code for it. Does anyone has any idea about the same, please reply to this thread.

**MindSpace** · 11-09-2009

If you want to have Vlookup in VBA then try to use the below code, hope it works for you.

Code:

Private Sub txtItem1_AfterUpdate()

Dim Item1 As Integer
Dim FileName1 As String
Dim MyRange1 As String

FileName = "e:\downloaded costs\itemlist.xls"
MyRange = "b1:b9999"

Item = txtItem1.Value
Set Rng = Worksheets(FileName1).Range(MyRange).Find(Item)

If Item <> "" Then
txtItem2.Visible = True
Else
txtItem2.Visible = False
End If

End Sub

**Durjaya** · 11-09-2009

I am also facing some issue with VBA. Can anyone provide me some code following are my situation

Worksheet "sheet1" with a 3-column range called "lst_Supplier" (suppliername + telephone + fax) , worksheet "sheet2" ,cell a1 with the requested supplier_name

Thanks in Advance.

**Ameya** · 11-09-2009

Try to use the following code i am sure this should work for you according to your situation.

Code:

Sub SampleVlookUp()
Dim rng_Requested As Range
Set rng_Requested = Sheets("Sheet2").Range("A1")
With Application.WorksheetFunction
Debug.Print .VLookup(rng_Requested.Value, Range("lst_Supplier"), 2, False) 'TELEPHONE
Debug.Print .VLookup(rng_Requested.Value, Range("lst_Supplier"), 3, False) 'FAX
End With
End Sub