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

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" wrote in message
...
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,
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.


-----Original Message-----
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" wrote in
message
...
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


.

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" wrote in message
...
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.


-----Original Message-----
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" wrote in
message
...
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


.

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

"Dan" wrote in
message
...
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.


-----Original Message-----
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" wrote in
message
...
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


.



.