Vlook up or another way

  • 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!

  • 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]

  • 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!

  • 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]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!