Pongwipat
06-10-2015, 12:59 AM
I want to code Vlookup and Match functionin UserForm but it display only one value in list of combobox.
For example, I add "a" in combobox1 (column B) and match with "1" in Culumn A and "1" in culumn F match again with "a-city, b-city , f-city, d-city,z-city" in culumn G. But only "a-city" is displayed in combobox2.
How can I code in Private Sub ComboBox1_Change() to get all values a-city, b-city , f-city, d-city,z-city showed in list of combobox2 ?
Anyone can help or seggest?
Private Sub ComboBox1_Change()
Dim i As Integer
With Application.WorksheetFunction
i = Val(.Index(Sheets("sheet1").Range("a2:a10000"), .Match(Me.ComboBox1, Sheets("Sheet1").Range("b2:b10000"), 0)))
'ComboBox2.Value = Application.WorksheetFunction.VLookup((i), Sheets("Sheet1").Range("F2:G10000"), 2, False)
ComboBox2.Value = .Index(Sheets("sheet1").Range("g2:g10000"), .Match(i, Sheets("Sheet1").Range("f2:f10000"), 0))
End With
End Sub
For example, I add "a" in combobox1 (column B) and match with "1" in Culumn A and "1" in culumn F match again with "a-city, b-city , f-city, d-city,z-city" in culumn G. But only "a-city" is displayed in combobox2.
How can I code in Private Sub ComboBox1_Change() to get all values a-city, b-city , f-city, d-city,z-city showed in list of combobox2 ?
Anyone can help or seggest?
Private Sub ComboBox1_Change()
Dim i As Integer
With Application.WorksheetFunction
i = Val(.Index(Sheets("sheet1").Range("a2:a10000"), .Match(Me.ComboBox1, Sheets("Sheet1").Range("b2:b10000"), 0)))
'ComboBox2.Value = Application.WorksheetFunction.VLookup((i), Sheets("Sheet1").Range("F2:G10000"), 2, False)
ComboBox2.Value = .Index(Sheets("sheet1").Range("g2:g10000"), .Match(i, Sheets("Sheet1").Range("f2:f10000"), 0))
End With
End Sub