Search cell contents in an entire workbook and return a specific cell from that sheet

Question

I'm trying to create a formula to get a string of text specified in A3 (text is T1234-1234) and to search this string in another workbook on a specified line, but on all sheets until a match is found and to return another cell within the sheet being searched.

Below is what I have working so far. This formula is only able to search a specified sheet (4372666_A.TXT) in cell A6 and return the value located in cell A7 only if A3 is located in cell A6 on the 4372666_A.TXT sheet.

There will be roughly 100 sheets that need to be searched at a time.

=IF(ISNUMBER(SEARCH(A3,'[EDICONFTESTEXCEL.xlsm]4372666_A.TXT'!$A$6)),LEFT(RIGHT('[EDICONFTESTEXCEL.xlsm]4372666_A.TXT'!$A$7,9),7),A3)

Answer 1

I would like to suggest you VBA code will search the Text String in all the Workbooks of any selected folder and return complete information like Workbook Name, Sheet Name, Cell Address & the Text String.

Enter the below written code as module in any existing sheet.

    Sub SearchWorkbooks()

    Dim xFso As Object
    Dim xFld As Object
    Dim xStrSearch As String
    Dim xStrPath As String
    Dim xStrFile As String
    Dim xOut As Worksheet
    Dim xWb As Workbook
    Dim xWk As Worksheet
    Dim xRow As Long
    Dim xFound As Range
    Dim xStrAddress As String
    Dim xFileDialog As FileDialog
    Dim xUpdate As Boolean
    Dim xCount As Long

    On Error GoTo ErrHandler

    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select the forlder"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If

    If xStrPath = "" Then Exit Sub
    xStrSearch = "Ravi"
    xUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    Set xOut = Worksheets.Add
    xRow = 1
    With xOut
        .Cells(xRow, 1) = "Workbook"
        .Cells(xRow, 2) = "Worksheet"
        .Cells(xRow, 3) = "Cell"
        .Cells(xRow, 4) = "Text in Cell"

        Set xFso = CreateObject("Scripting.FileSystemObject")
        Set xFld = xFso.GetFolder(xStrPath)
        xStrFile = Dir(xStrPath & "\*.xlsm*")

        Do While xStrFile <> ""
            Set xWb = Workbooks.Open(Filename:=xStrPath & "\" & xStrFile, UpdateLinks:=0, ReadOnly:=True, AddToMRU:=False)
            For Each xWk In xWb.Worksheets
                Set xFound = xWk.UsedRange.Find(xStrSearch)
                If Not xFound Is Nothing Then
                    xStrAddress = xFound.Address
                End If
                Do
                    If xFound Is Nothing Then
                        Exit Do
                    Else
                        xCount = xCount + 1
                        xRow = xRow + 1
                        .Cells(xRow, 1) = xWb.Name
                        .Cells(xRow, 2) = xWk.Name
                        .Cells(xRow, 3) = xFound.Address
                        .Cells(xRow, 4) = xFound.value
                    End If
                    Set xFound = xWk.Cells.FindNext(After:=xFound)
                Loop While xStrAddress <> xFound.Address
            Next
            xWb.Close (False)
            xStrFile = Dir
        Loop
        .Columns("A:D").EntireColumn.AutoFit
    End With
    MsgBox xCount & "cells have been found", , "Search String Across WBKs"
ExitHandler:
    Set xOut = Nothing
    Set xWk = Nothing
    Set xWb = Nothing
    Set xFld = Nothing
    Set xFso = Nothing
    Application.ScreenUpdating = xUpdate
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

How it Works:

• Run this Macro.
• It will open the File Manager Dialogue box.
• Select the Folder in which all workbooks are stored.
• Finish with Ok.

Excel will display Message Box that how many text strings have been found. Finally New worksheet will be created to display results looks like shown below.

N.B.

• xStrSearch = "Ravi" as well as .Columns("A:D").EntireColumn.AutoFit , are editable.

• You can replace "Ravi" with another string and Column A:D with other.

Answer 2

Here's a nice and simple UDF that works in a similar manner to the builtin SEARCH() function, except it searches all the worksheets and returns the value of a different cell instead of the index:

'============================================================================================
' Module     : <any standard module>
' Version    : 0.1.0
' Part       : 1 of 1
' References : N/A
' Source     : https://superuser.com/a/1332265/763880
'============================================================================================
Option Explicit

Public Function SEARCHALLSHEETS _
                ( _
                           ByVal find_text As String, _
                           ByVal within_cell As Range, _
                           ByVal return_cell As Range _
                ) _
       As Variant

  Dim strWithinCell As String: strWithinCell = within_cell.Address
  Dim strReturnCell As String: strReturnCell = return_cell.Address

  Dim wkstWorksheet As Worksheet
  For Each wkstWorksheet In within_cell.Parent.Parent.Worksheets
    If InStr(wkstWorksheet.Range(strWithinCell), find_text) > 0 Then
      Dim varReturnValue As Variant
      varReturnValue = wkstWorksheet.Range(strReturnCell).Value2
      Exit For
    End If
  Next wkstWorksheet
  SEARCHALLSHEETS = IIf(IsEmpty(varReturnValue), CVErr(xlErrValue), varReturnValue)

End Function

Usage: SEARCHALLSHEETS(find_text, within_cell, return_cell)

Once it's installed, you use it by supplying the search target cell and return cell in any one worksheet of a workbook and it will search all the worksheets of that workbook.

If it can't find the search text in any of the worksheets, it returns the #VALUE! error just like the SEARCH() function does.

Your formula needs to be re-written to detect the error instead of a number to determine whether or not a match has been found. Plus, it also needs to be modified to use the new return value.

This is your updated formula:

=IFERROR(LEFT(RIGHT(SEARCHALLSHEETS(A3,[EDICONFTESTEXCEL.xlsm]Sheet1!$A$6,[EDICONFTESTEXCEL.xlsm]Sheet1!$A$7),9),7),A3)

There is an alternate, more direct conversion of your formula, but it is much longer. I have included it here for your reference:

=IF(NOT(ISERROR(SEARCHALLSHEETS(A3,'[EDICONFTESTEXCEL.xlsm]4372666_A.TXT'!$A$6,'[EDICONFTESTEXCEL.xlsm]4372666_A.TXT'!$A$7))),LEFT(RIGHT(SEARCHALLSHEETS(A3,'[EDICONFTESTEXCEL.xlsm]4372666_A.TXT'!$A$6,'[EDICONFTESTEXCEL.xlsm]4372666_A.TXT'!$A$7),9),7),A3)