Forum Discussion

filipiovitu's avatar
filipiovitu
Copper Contributor
May 01, 2024

Search for a value in entire workbook, return the entire row where value was found

Hello All,

 

Example: I have 10 Invoice numbers (C093705926 C093705927 C093725527 C093750757 C093750759 C093769955 C093769956 C093789382 C093808525 C093808526) and need to search these in a workbook where they are all located.

 

1. Can I search for them at the same time?

2. Even if I cannot search for them at the same time, is it possible for the search to return the entire row "C093705926" is located in?

3. What happens if I have to do this for 200 invoices, will this mess up my workbook?

 

Thank you!

Excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    May 01, 2024

    filipiovitu 

    Try this macro:

    Sub ListEm()
    Dim wbkSource As Workbook
    Dim wshSource As Worksheet
    Dim rngSource As Range
    Dim strAddress As String
    Dim wbkTarget As Workbook
    Dim wshTarget As Worksheet
    Dim varSearch As Variant
    Dim lngRow As Long
    Application.ScreenUpdating = False
    Set wbkSource = ActiveWorkbook
    Set wbkTarget = Workbooks.Add(xlWBATWorksheet)
    Set wshTarget = wbkTarget.Worksheets(1)
    For Each wshSource In wbkSource.Worksheets
        For Each varSearch In Array("C093705926", "C093705927", "C093725527", _
                "C093750757", "C093750759", "C093769955", "C093769956", _
                "C093789382", "C093808525", "C093808526")
            Set rngSource = wshSource.Range("C:C").Find(What:=varSearch, LookAt:=xlWhole)
            If Not rngSource Is Nothing Then
                strAddress = rngSource.Address
                Do
                    lngRow = lngRow + 1
                    rngSource.EntireRow.Copy Destination:=wshTarget.Range("A" & lngRow)
                    Set rngSource = wshSource.Range("C:C").Find(What:=varSearch, _
                            After:=rngSource, LookAt:=xlWhole)
                    If rngSource Is Nothing Then Exit Do
                Loop Until rngSource.Address = strAddress
            End If
        Next varSearch
    Next wshSource
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Resources