Resubmit of Macro needed to search 2 columns

[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?

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

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

 

[Bob Phillips]

Private Sub UserForm_Initialize()
Dim itm
Dim coll As Collection
Dim i As Long

Set coll = New Collection
On Error Resume Next
For i = 1 To ActiveSheet.UsedRange.Rows.Count
coll.Add Range("A" & i).Value, Range("A" & i).Value
Next i
On Error GoTo 0

For Each itm In coll
Me.cbo1.AddItem itm
Next

End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Incidental]

Hi there

I just refreshed the page and I see that Bob has posted a very good
reply to your problem so I am posting this as another option for how
to get round this though I would say to go with Bobs code as it is
much more efficient than the code below which uses a findnext method

steve

Option Explicit
Dim LastRow As Integer
Dim i As Integer
Dim MyRng As Range
Dim Fcell As Range
Dim FcellAdd As String
Dim FindRef As String

Private Sub CommandButton1_Click()

LastRow = [a65535].End(xlUp).Row
Set MyRng = Range("A1:A" & LastRow)

FindRef = "apple"

i = 1

Do While i <= 3
Set Fcell = MyRng.Find(FindRef, Lookat:=xlWhole)
If Not Fcell Is Nothing Then
FcellAdd = Fcell.Address
Do
Set Fcell = MyRng.FindNext(Fcell)
Loop While Not Fcell Is Nothing And Fcell.Address <> FcellAdd
ComboBox1.AddItem Fcell.Value
End If
i = i + 1
If i = 2 Then
FindRef = "pear"
Else
FindRef = "grape"
End If

Loop

End Sub

 

[Mekinnik]

Mr. Phillips I tried your code and it does not populate the first combobox
with anything?

 

[Bob Phillips]

Works fine in my tests, so it suggests that either column A has no data, the
activesheet is incorrect, or maybe cbo1 is not the combo name.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mekinnik]

I found the problem it was a type O on my part.
Thank You,
Mr. Phillips