Return location of specific text in workbook

glynn1969

Board Regular
Joined
Nov 24, 2018
Messages
88
Office Version
  1. 365
Platform
  1. Windows
Hello, I have a workbook with many tabs on containing lots of data.

I am looking for some vba that will search through all the tabs in the workbook and return a list of the tabs/cells that contain certain text, with the list showing in column A and B of a tab called "Check"

I would like it so that the certain text to look for is entered via a message box as the macro starts it's run.

Would be even better if the search could work on five text values and return results in different columns on "Check" tab.

Any guidance much appreciated.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Change the text in red to suit your needs.
Rich (BB code):
Sub Findtext()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, txtArr As Variant, i As Long, desWS As Worksheet, fnd As Range
    txtArr = Array("Text1", "Text2", "Text3", "Text4", "Text5")
    Set desWS = Sheets("Check")
    For Each ws In Sheets
        If ws.Name <> "Check" Then
            For i = LBound(txtArr) To UBound(txtArr)
                Set fnd = ws.UsedRange.Cells.Find(txtArr(i), LookIn:=xlValues, lookat:=xlWhole)
                If Not fnd Is Nothing Then
                    With desWS
                        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 2).Value = Array(ws.Name, fnd.Address(0, 0))
                    End With
                End If
            Next i
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Change the text in red to suit your needs.
Rich (BB code):
Sub Findtext()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, txtArr As Variant, i As Long, desWS As Worksheet, fnd As Range
    txtArr = Array("Text1", "Text2", "Text3", "Text4", "Text5")
    Set desWS = Sheets("Check")
    For Each ws In Sheets
        If ws.Name <> "Check" Then
            For i = LBound(txtArr) To UBound(txtArr)
                Set fnd = ws.UsedRange.Cells.Find(txtArr(i), LookIn:=xlValues, lookat:=xlWhole)
                If Not fnd Is Nothing Then
                    With desWS
                        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 2).Value = Array(ws.Name, fnd.Address(0, 0))
                    End With
                End If
            Next i
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thanks for this works well.

Is there a way for tge search text to be entered via a prompt box to display at the start if the macro, rather than in the macro itself.

It's just I will have various searches to perform and would rather not have people fiddling in the macro itself
 
Upvote 0
Change the text in red to suit your needs.
Rich (BB code):
Sub Findtext()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, txtArr As Variant, i As Long, desWS As Worksheet, fnd As Range
    txtArr = Array("Text1", "Text2", "Text3", "Text4", "Text5")
    Set desWS = Sheets("Check")
    For Each ws In Sheets
        If ws.Name <> "Check" Then
            For i = LBound(txtArr) To UBound(txtArr)
                Set fnd = ws.UsedRange.Cells.Find(txtArr(i), LookIn:=xlValues, lookat:=xlWhole)
                If Not fnd Is Nothing Then
                    With desWS
                        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 2).Value = Array(ws.Name, fnd.Address(0, 0))
                    End With
                End If
            Next i
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Really sorry, can it be set as a partial match rather than an exact...my error sorry
 
Upvote 0
Can you post a few examples of what a partial match would look like? Also, will you always be searching for exactly five values or can it be less than five values?
 
Upvote 0
I don't see the examples or the answer to my question.
 
Upvote 0
Thank you. I'll answer the second question first. It will not always be 5 matches, could be less.

As for the partial match, as an example I woukd like it so that if I searched for the word "fish" then it would return match for cells that had "yellow fish" or "fishcake" or "fish finger"...basically having "fish" anywhere in the text being. searched.

Thanks in advance for taking your time out to look at this.
 
Upvote 0

Forum statistics

Threads
1,222,742
Messages
6,167,922
Members
452,156
Latest member
onkey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top