Macro needed to search 2 columns

[Mekinnik]

I am trying to create code for my user form that would use a combobox
selection as the first criteria and search cloumn 'B' for every of that value
that has the name within the same row or something like that and add the
values to a combobox drop down list. I would look something like this:

col. 'A' col. 'B'
apple red
apple green
apple yellow
pear green
grape purple
apple pink

for every 'apple' in column 'A' it will add the color in column 'B' to the
drop down list.

I would like this to happen in the userform initialize event, so that when
the second userform opens the dropdown list is allready populated, also if it
doesn't find any matches it will show a msgbox.

 

[crferguson]

Based striclty on your sample data and using to generic forms (frm1
and frm2) with 2 generic combos (cbo1 and cbo2), here's the code that
works for me:

Private Sub UserForm_Initialize()
Dim arr1, dX As Double
'fill an array with the values from column A
'change A1 to A2 if you have a header row
arr1 = Range("A1:A" & ActiveSheet.UsedRange.Rows.Count)

'loop through the array and add the items to the
'first combo box
For dX = LBound(arr1) To UBound(arr1)
Me.cbo1.AddItem arr1(dX, 1)
Next

End Sub

Private Sub cbo1_Change()
Dim sSelected As String
Dim arr2
Dim dX As Double, dCount As Double

'get the selected value, if any
sSelected = Me.cbo1.Value
'set the match counter to initial vaule of 0
dCount = 0

'loop through column A looking for a match to
'the selected value
For dX = 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(dX, 1).Value = sSelected Then
'if the values match, then add value from
'column B to the second form's combobox
frm2.cbo2.AddItem Cells(dX, 2).Value
'increment the match counter by 1
dCount = dCount + 1
End If
Next

If Not dCount = 0 Then
'if matches were found, show the second form and
'unload the first
frm2.Show
Unload Me
Else
'if no matches were found, alert the user
MsgBox "No matches found for: " & sSelected
End If
End Sub

Hope that helps!
Cory

 

[Mekinnik]

How could I change the first combobox to only show the first instance of a
value/text? Right now the way it is it shows all the values in column A, so
like I showed the combobox would only show apple,pear,grape, not all the
apples?