Sometimes, I bite off more than I can chew and this is one of those times.
I have an Issue Log where I keep incoming calls recorded. When a store calls in I wanted to be able to look at all the "logged calls" for that store. I found the code below that helps in doing some of that. Once I have selected the store number in the ComboBox and execute through the CommandButton the ListBox fills out.
The drawback is this only gives me a single line item, and invariably there's more than just one issue in the logbook for a particular store.
How can I change this up a little more to enable it to find multiple records?
Dim rngToSearch As Range
Dim rngToFind As Range
Dim valToFind As Variant
Dim arrClearList()
valToFind = ComboBox1.Value 'Edit ComboBox1 to your ComboBox name
With Worksheets("Data")
Set rngToSearch = .Columns("A")
End With
Set rngToFind = rngToSearch.Find(What:=valToFind, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rngToFind Is Nothing Then
'Call ClearList(Me.ListBox1) 'Optional to clear existing list
ListBox1.AddItem
With ListBox1
.List(.ListCount - 1, 0) = rngToFind.Value 'ID Col A
.List(.ListCount - 1, 1) = rngToFind.Offset(0, 0).Value 'Store #
.List(.ListCount - 1, 2) = rngToFind.Offset(0, 1).Value 'Call Date
.List(.ListCount - 1, 3) = rngToFind.Offset(0, 2).Value 'Build By Date
.List(.ListCount - 1, 4) = rngToFind.Offset(0, 4).Value 'Build Status
.List(.ListCount - 1, 5) = rngToFind.Offset(0, 13).Value 'Store Contact
.List(.ListCount - 1, 6) = rngToFind.Offset(0, 11).Value 'PTS #
.List(.ListCount - 1, 7) = rngToFind.Offset(0, 12).Value 'SOW #
End With
Else
MsgBox valToFind & " not found in worksheet."
End If
Bookmarks