PDA

View Full Version : VBA UerForm : Match and Vlookup multi value in combobox



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

Kenneth Hobs
06-10-2015, 06:26 AM
Welcome to the forum!

First, put this in a Module.

' Chip Pearson, http://www.cpearson.com/excel/FindAll.aspx' Kenneth Hobson, http://www.vbaexpress.com/forum/showthread.php?t=38802
Function FoundRanges(fRange As Range, fStr As String) As Range
Dim objFind As Range
Dim rFound As Range, FirstAddress As String

With fRange
Set objFind = .Find(What:=fStr, After:=fRange.Cells((fRange.Rows.Count), fRange.Columns.Count), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=True)
If Not objFind Is Nothing Then
Set rFound = objFind
FirstAddress = objFind.Address
Do
Set objFind = .FindNext(objFind)
If Not objFind Is Nothing Then Set rFound = Union(objFind, rFound)
Loop While Not objFind Is Nothing And objFind.Address <> FirstAddress
End If
End With
Set FoundRanges = rFound
End Function
Replace your Sub with this one.

Private Sub ComboBox1_Change() Dim f As Range

Set f = Range("B2", Range("B" & Rows.Count).End(xlUp)).Find(ComboBox1.Value).Offset(, -1)
If f Is Nothing Then Exit Sub

Set f = FoundRanges(Range("F2", Range("F" & Rows.Count).End(xlUp)), f.Value2)
If f Is Nothing Then Exit Sub
ComboBox2.List = f.Offset(, 1).Value
End Sub

snb
06-10-2015, 07:41 AM
I'd use:


Private Sub ComboBox1_Change()
ComboBox2.List = Split(Replace(Join(Filter([transpose(F1:F100&G1:G100)], ComboBox1.ListIndex + 1), "|"), ComboBox1.ListIndex + 1, ""), "|")
End Sub

Pongwipat
06-10-2015, 09:34 PM
Thank you so much for you help.

But I have one problem when I run userform on other sheets (except Sheet1), it is not working.

I try to put "Sheets!(Sheet1)" in front of " Range " like this

Sheets!(Sheet1).Range() ............


but It isn't also working

How can fix this problem?

Kenneth Hobs
06-11-2015, 05:58 AM
MsgBox Worksheets("Sheet1").Range("A1").Value
'or
MsgBox Sheets("Sheet1").Range("A1").Value

You can also use Activate if you want to keep the default ActiveSheet object in play.