Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB's equivalent to VLOOKUP?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB's equivalent to VLOOKUP?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB's equivalent to VLOOKUP?
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB's equivalent to VLOOKUP?
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB's equivalent to VLOOKUP?
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function Equivalent need | Excel Worksheet Functions | |||
Equivalent of Alt+Tab for going between worksheets? | Excel Discussion (Misc queries) | |||
Maxif equivalent | Excel Worksheet Functions | |||
How do I create an equivalent VLOOKUP function using FIND? | Excel Worksheet Functions | |||
Lotus Equivalent | Excel Discussion (Misc queries) |