VBA Code To Search Workbook For Strings And Paste String + SheetName

[ifechi]

Hi Guys,

First time newbie here.

I have a workbook with several sheets.

I am trying to search using row values of one sheet across the workbook.

When there is a match, the string searched for and the sheet name where the string is found should be recorded.

I have no idea if this is doable so I am asking the gurus.

Thanks.

 

[Fishboy]

Hi Guys,

First time newbie here.

I have a workbook with several sheets.

I am trying to search using row values of one sheet across the workbook.

When there is a match, the string searched for and the sheet name where the string is found should be recorded.

I have no idea if this is doable so I am asking the gurus.

Thanks.

Hi ifechi, welcome to the boards.

OK, so assuming you have a sheet call ListSheet (you can obviously amend this within the code as required) which has a list of desired search values in column A starting in cell A1, you could try the following code. It will look at each cell in the list of search values, then check every sheet which isn't called ListSheet for that value. If the value is found the adjcent cell in column B of the ListSheet will update with the sheet name and cell address that the search value was found in.

Sub FindValues()
' Defines variables
Dim Cell As Range, cRange As Range, sRange As Range, ws As Worksheet
    ' Defines LastRow as the last row of data in column A in the ListSheet
    LastRow = Sheets("ListSheet").Cells(Rows.Count, "A").End(xlUp).Row
        ' Sets range to check for as Sheet1 A1 to last row
        Set cRange = Sheets("ListSheet").Range("A1:A" & LastRow)
            ' For each sheet in the workbook
            For Each ws In ActiveWorkbook.Worksheets
                ' If the sheet name is not ListSheet
                If ws.Name <> "ListSheet" Then
                ' Sets search range as Sheet2 A2 to last row
                    Set sRange = ws.UsedRange
                            ' For each cell in the check range
                            For Each Cell In cRange
                                ' String to find equals cell value
                                FindString = Cell.Value
                                    ' With the search range
                                    With sRange
                                        ' Set Rng as the cell where the value is found
                                        Set Rng = .Find(What:=FindString, _
                                                        After:=.Cells(1), _
                                                        LookIn:=xlValues, _
                                                        LookAt:=xlWhole, _
                                                        SearchOrder:=xlByRows, _
                                                        SearchDirection:=xlPrevious, _
                                                        MatchCase:=False)
                                            ' If Rng exists then
                                            If Not Rng Is Nothing Then
                                                ' Update the adjacent cell with the name of the sheet Rng is on and what cell it was found in
                                                Cell.Offset(0, 1).Value = ws.Name & " " & Rng.Address
                                            End If
                                    End With
                            ' Move to next cell in check range
                            Next Cell
                End If
            ' Check next sheet
            Next ws
End Sub

Is that any good to you?