Vlook up or another way

Brister · Jun 8th 2006

I have a userform where I have 2 comboboxes. The first combobox shows the the first column (only 1 of each) and the second comboBox shows me the secondary list that correlates to the valuse in the first from column B. Now I have a text box that I am trying to get the value from column C depending on what I have in the first 2 comboboxes. What is the easiest way to do it? This is all in VB since it is a UserForm, and using Vlookup seems to be too many lines if I go that route. Is there a way to use Index and Match in VB where it would be more efficient? I attached just a sample of how the data would be layed out in the Excel sheet.

Thanks for your help!

**Andy Pope** · Jun 8th 2006

Re: Vlook up or another way

Hi,

This stores the relavent information in column 2 of the comboboxes

[vba]Private Sub ComboBox1_Change()

Dim lngRow As Long

ComboBox2.Clear
With Sheet1
lngRow = .Range(ComboBox1.List(ComboBox1.ListIndex, 1)).Row
Do
ComboBox2.AddItem .Cells(lngRow, 2)
ComboBox2.List(ComboBox2.ListCount - 1, 1) = .Cells(lngRow, 3)
lngRow = lngRow + 1
Loop While StrComp(.Cells(lngRow, 1), ComboBox1.Value, vbTextCompare) = 0
End With
End Sub

Private Sub ComboBox2_Change()

If ComboBox2.ListIndex >= 0 Then
TextBox1.Text = ComboBox2.List(ComboBox2.ListIndex, 1)
End If

End Sub
Private Sub UserForm_Initialize()

Dim lngRow As Long

With Sheet1
lngRow = 2
Do While .Cells(lngRow, 1) <> ""
If StrComp(.Cells(lngRow, 1), .Cells(lngRow - 1, 1), vbTextCompare) Then
ComboBox1.AddItem .Cells(lngRow, 1)
ComboBox1.List(ComboBox1.ListCount - 1, 1) = .Cells(lngRow, 1).Address
End If
lngRow = lngRow + 1
Loop
End With

End Sub[/vba]

carlmack · Jun 8th 2006

Re: Vlook up or another way

I'm bit late I see. Anyway a different approach attached.

Brister · Jun 8th 2006

Re: Vlook up or another way

Thanks for the response. Andy, that is the idea I am looking for, but I am trying to make heads or tails out of the code which makes somewhat sense (Ive never seen the StrComp before so I learned something new) but what doesn't make sense is the textBox1 value.

The line:

Code

If ComboBox2.ListIndex >= 0 Then
        TextBox1.Text = ComboBox2.List(ComboBox2.ListIndex, 1)
    End If

I am looking at that, and I don't understand how based on the combobox2 listindex, it is able to pull the next column data. How can I pull the last column (D) for that data? Thanks for your help!

**Andy Pope** · Jun 8th 2006

Re: Vlook up or another way

I didn't have time to comment it fully as I was leaving work.

Try this revised version with comments.
I have also made the 2 columns within each combobox visible so you can see what is going on more clearly.
[vba]Private Sub ComboBox1_Change()

Dim lngRow As Long

' empty combobox2
ComboBox2.Clear
With Sheet1
' get starting row number for selected item in combobox1
lngRow = .Range(ComboBox1.List(ComboBox1.ListIndex, 1)).Row
' store contents of columns B and D in combobox2
Do
ComboBox2.AddItem .Cells(lngRow, 2) ' column B information
ComboBox2.List(ComboBox2.ListCount - 1, 1) = .Cells(lngRow, 4).Text ' column D information
lngRow = lngRow + 1
Loop While StrComp(.Cells(lngRow, 1), ComboBox1.Value, vbTextCompare) = 0
End With
End Sub

Private Sub ComboBox2_Change()

' display the value in column 2 of combobox2
If ComboBox2.ListIndex >= 0 Then
TextBox1.Text = ComboBox2.List(ComboBox2.ListIndex, 1)
End If

End Sub
Private Sub UserForm_Initialize()

Dim lngRow As Long

' this code is only required in order to more easily
' demonstrate the use of multiple columns in a list/combolist

ComboBox1.ColumnCount = 2
ComboBox2.ColumnCount = 2

' populate combobox1 with unique values from column A
With Sheet1
lngRow = 2
Do While .Cells(lngRow, 1) <> ""
If StrComp(.Cells(lngRow, 1), .Cells(lngRow - 1, 1), vbTextCompare) Then
ComboBox1.AddItem .Cells(lngRow, 1) ' store contents of column A
ComboBox1.List(ComboBox1.ListCount - 1, 1) = .Cells(lngRow, 1).Address ' store address of item
End If
lngRow = lngRow + 1
Loop
End With

End Sub[/vba]

Vlook up or another way

Participate now!

Share