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]

Try:

Sub Findtext()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, txtArr As Variant, i As Long, desWS As Worksheet, fnd As Range, sText2 As String
    sText = InputBox("Please enter the search criteria separated by a comma:" & Chr(10) & "For example: text1,text2,text3")
    If sText = "" Then Exit Sub
    txtArr = Split(sText, ",")
    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:=xlPart)
                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