VB's equivalent to VLOOKUP?

[Dan]

Is there a VB Equivalent to Excel's Vlookup Formula.

Lets say that I have three names in a list box.

1. Dan
2. Jim
3. Doug

The Variable I want to pass on is a value associated with
one of the 3 items.

Name (From Listbox), Salary

Dan, 50000
Jim, 60000
Ted, 70000

Lets say my variable is X.

How would I get X to be the set equal to the salary when
one of the names is selected.

I know I could create another varaiable called Y and set
that equal to the lstbox.value and then use an if then
statement. But with a lot of names that could become very
labor intensive to create. Any ideas.

Thanks

Dan

 

[Bernie Deitrick]

Dan,

Put your lookup table is in a range, then simply use

Application.Worksheetfunction.Vlookup(....)

The big difference is that instead of using the function as you do in the
worksheet:

=VLOOKUP(D1, A1:B3, 2, False)

You would use:

Y = value from the Listbox
X = Application.Worksheetfunction.Vlookup(Y, Range("A1:B3"), 2, False)

HTH,
Bernie
MS Excel MVP

 

[Dan]

Bernie,
I actually need to keep this entirely in the VBA. The app
I am using is not Excel. I just queried this group
because I have gotten ggod answers here before. I
actually need to do this in VBA for MS Project. I should
have said that in my original post. Thank You for the
quick response though.

 

[Bernie Deitrick]

Dan,

Put your values into a 2 dimensional array. Then step through your array of
values, and check the first dimension's value against what you are looking
for, then simply read the 2nd dimensions value, along the lines of:

Dim myArray(1 To 3, 1 To 2) As Variant
Dim i As Integer
Dim X As String
Dim Y As Long

myArray(1, 1) = "Dan"
myArray(2, 1) = "Jim"
myArray(3, 1) = "Ted"
myArray(1, 2) = 50000
myArray(2, 2) = 60000
myArray(3, 2) = 70000

X = "Ted" 'Read this from Listbox

For i = 1 To 3
If myArray(i, 1) = X Then
Y = myArray(i, 2)
Exit For
End If
Next i

MsgBox X & " makes $" & Y

HTH,
Bernie
MS Excel MVP

 

[Dan]

Thank you for your help.

-----Original Message-----
Dan,

Put your values into a 2 dimensional array. Then step through your array of
values, and check the first dimension's value against what you are looking
for, then simply read the 2nd dimensions value, along the lines of:

Dim myArray(1 To 3, 1 To 2) As Variant
Dim i As Integer
Dim X As String
Dim Y As Long

myArray(1, 1) = "Dan"
myArray(2, 1) = "Jim"
myArray(3, 1) = "Ted"
myArray(1, 2) = 50000
myArray(2, 2) = 60000
myArray(3, 2) = 70000

X = "Ted" 'Read this from Listbox

For i = 1 To 3
If myArray(i, 1) = X Then
Y = myArray(i, 2)
Exit For
End If
Next i

MsgBox X & " makes $" & Y

HTH,
Bernie
MS Excel MVP




.