search all worksheets based upon textbox value and add to listbox

[Jaye7]

I have a userform textbox and I want to search all worksheets for the value in the textbox and display the results in a listbox, there could be multiple results.

Information.
Userform1
Textbox1 (search text entered)
Listbox1 (shows results of worksheet name where found + cell text where found + cell address where found)

Msgbox show the count of the number of times that the text was found and also each cell where it was found with a line space between each result.

I tried the following but it didn't work.

ListBox1.Clear

Dim ws As Worksheet, Found As Range

Dim myText As String, FirstAddress As String, List1 As String

Dim AddressStr As String, foundNum As Integer

myText = Me.Textbox1


For Each ws In ThisWorkbook.Worksheets

With ws

Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

If Not Found Is Nothing Then

FirstAddress = Found.Address

Do

foundNum = foundNum + 1

AddressStr = AddressStr & "in Sheet: " & .Name & " " & "Cell address: -- " & Found.Address & vbCrLf

Set Found = .UsedRange.FindNext(Found)

ListBox1.AddItem AddressStr

Loop While Not Found Is Nothing And Found.Address <> FirstAddress

myNext:

End If

End With

Next ws

If Len(AddressStr) Then

MsgBox "Found :""" & myText & """ " & foundNum & " times." & vbLf & AddressStr, vbOKOnly, myText & " found in these cells"

Else:

MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation

End If

 

[Jeffrey Mahoney]

Does this work for you?

ListBox1.Clear

  Dim ws As Worksheet
  Dim myText As String
  Dim foundNum As Integer
  Dim Cel As Range, Rng As Range
  
  myText = Me.Textbox1
  
  
  For Each ws In ThisWorkbook.Worksheets
    Set Rng = ws.Range("A1:X10").UsedRange
    For Each Cel In Rng
      If InStr(Cel.Value, myText) Then
        ListBox1.AddItem "'" & ws.Name & "'!" & Cel.Address(0, 0)
        foundNum = foundNum + 1
      End If
    Next Cel
  Next ws
  
  If Len(AddressStr) Then
    MsgBox "Found :" & myText & " " & foundNum & " times."
  Else
    MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
  End If

 

[Jaye7]

it errors on the following line as AddressStr is not present in your dim statement or a reference to it, also I want it to check the whole range in each sheet, as some sheets may have 20,000 lines etc

If Len(AddressStr) Then

 

[Jeffrey Mahoney]

Well you can add the DIM statement in. But that is related to your summation at the end. I would change that to:
If FoundNum > 0 Then

Try to run it and see how slow it is.

 

[Jaye7]

sorry but it's not working, I now get an error on the following line.

Set Rng = ws.Range("A1:X10").UsedRange

Error message 438, object doesn't support this property or method

 

[Jeffrey Mahoney]

Change that to
Set Rng = ws.UsedRange

 

[Akuini]

I tried the following but it didn't work.

Can you describe in more detail what "didn't work" mean?

 

[Jaye7]

thanks for your help Jeffrey,

I ended up using the following code as it gives me everything I need, I added some additional code also.

ListBox1.Clear


Dim ws As Worksheet, Found As Range
Dim myText As String, FirstAddress As String, List1 As String
Dim AddressStr As String, foundNum As Integer

myText = Me.Textbox1

For Each ws In ThisWorkbook.Worksheets
With ws
Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not Found Is Nothing Then
FirstAddress = Found.Address

Do
foundNum = foundNum + 1
AddressStr = AddressStr & "in Sheet: " & .Name & " " & "Cell address: -- " & Found.Address & vbCrLf
Set Found = .UsedRange.FindNext(Found)

'ListBox1.AddItem AddressStr

   ListBox1.AddItem
   ListBox1.List(foundNum - 1, 0) = "Found in sheet: "
   ListBox1.List(foundNum - 1, 1) = ws.Name
   ListBox1.List(foundNum - 1, 2) = " -- Cell address: "
   ListBox1.List(foundNum - 1, 3) = Found.Address
   ListBox1.List(foundNum - 1, 4) = " -- Text: "
   ListBox1.List(foundNum - 1, 5) = Found.Value

Loop While Not Found Is Nothing And Found.Address <> FirstAddress

myNext:
End If
End With
Next ws

If Len(AddressStr) Then
MsgBox "Found :""" & myText & """ " & foundNum & " times." & vbLf & AddressStr, vbOKOnly, myText & " found in these cells"
Else:
MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If