Return location of specific text in workbook

[glynn1969]

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.

 

[mumps]

Change the text in red to suit your needs.

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

 

[glynn1969]

Change the text in red to suit your needs.

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

 

[glynn1969]

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

 

[glynn1969]

Change the text in red to suit your needs.

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

 

[glynn1969]

Really sorry, can it be set as a partial match rather than an exact...my error sorry

 

[mumps]

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?

 

[glynn1969]

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

 

[mumps]

I don't see the examples or the answer to my question.

 

[glynn1969]

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.