sharky12345
Well-known Member
- Joined
- Aug 5, 2010
- Messages
- 3,431
- Office Version
- 2016
- Platform
- Windows
I've come across this as a method for searching a sheet for a value and displaying the results in a Listbox;
I'd like some help to tweak it if someone can assist please...
What I need is for the Listbox to display the values from Columns A:G in the listbox if the item searched for has been found in each row.
For example, if I searched for the value 'Email' and that appeared in cell A20 and D45 then I'd want rows 20 and 45 to appear in the Listbox.
Makes sense?
Code:
Sub FindAllMatches()
Dim SearchRange As Range
Dim FindWhat As Variant
Dim FoundCells As Range
Dim FoundCell As Range
Dim arrResults() As Variant
Dim lFound As Long
If Len(Me.Controls.Item("TextBox_Find").Value) > 1 Then
Set SearchRange = ActiveSheet.UsedRange.Cells
FindWhat = Me.Controls.Item("TextBox_Find").Value
Set FoundCells = FindAll(SearchRange:=SearchRange, FindWhat:=FindWhat, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, BeginsWith:=vbNullString, EndsWith:=vbNullString, BeginEndCompare:=vbTextCompare)
If FoundCells Is Nothing Then
ReDim arrResults(1 To 1, 1 To 2)
arrResults(1, 1) = "No Results"
Else
ReDim arrResults(1 To FoundCells.Count, 1 To 2)
lFound = 1
For Each FoundCell In FoundCells
arrResults(lFound, 1) = FoundCell.Value
arrResults(lFound, 2) = FoundCell.Address
lFound = lFound + 1
Next FoundCell
End If
Me.Controls.Item("ListBox_Results").List = arrResults
Else
Me.Controls.Item("ListBox_Results").Clear
End If
End Sub
I'd like some help to tweak it if someone can assist please...
What I need is for the Listbox to display the values from Columns A:G in the listbox if the item searched for has been found in each row.
For example, if I searched for the value 'Email' and that appeared in cell A20 and D45 then I'd want rows 20 and 45 to appear in the Listbox.
Makes sense?