Vlookup

[jl45]

If hoping that this should be real easy!(im a real beginner!)

I have a form with a text box and a button

A user enters some text in the textbox tehn presses the button.

The code behind the button should take the value in the textbox perform a vlookup on a stored spreadsheet and return the value of the vlookup in a msgbox

I dont know how to do this please help...

I have the following so far (text1 is the textbox) which doesnt work

src="c:\data.xls"
lookuprange= range("B8:c2000")
msgbox vlookup(text1,lookuprange,2,false)


where do I go from here?

 

[byundt]

I assume that you are working in Excel. If so, try this:

Dim src As String
Dim lookuprange As Range
src="c:\data.xls"
Set lookuprange= Workbooks("data.xls").Worksheets("Sheet1").range("B8:C2000")
msgbox Application.vlookup(text1,lookuprange,2,false)

This approach requires that data.xls be open. I assume that you have already assigned a value to variable text1. I also assume that text1 exists in lookuprange--because you have no provision to handle the error if it doesn't exist.

If the worksheet containing B8:C2000 is active, then you don't need to qualify it with the name of the workbook and worksheet. Just use:
Set lookuprange=Range("B8:C2000")
You need the Set because lookuprange is a Range object variable.

To use worksheet functions in VBA, you can prefix them either like Application.Vlookup or WorksheetFunction.Vlookup. I use the former because it is shorter.

 

[Kryzsoccer]

you wrote "text1 is the textbox", if this is the case then use Text1.text when doing the Vlookup