Find all matches in workbook using Excel VBA

by

Using the Range.Find method, as pointed out above, along with a loop for each worksheet in the workbook, is the fastest way to do this. The following, for example, locates the string “Question?” in each worksheet and replaces it with the string “Answered!”.

Sub FindAndExecute()

Dim Sh As Worksheet
Dim Loc As Range

For Each Sh In ThisWorkbook.Worksheets
    With Sh.UsedRange
        Set Loc = .Cells.Find(What:="Question?")
        If Not Loc Is Nothing Then
            Do Until Loc Is Nothing
                Loc.Value = "Answered!"
                Set Loc = .FindNext(Loc)
            Loop
        End If
    End With
    Set Loc = Nothing
Next

End Sub

More Related Contents:

  1. Duplicate number and it value in column EXCEL [closed]
  2. Loop through files in a folder using VBA?
  3. How to error handle 1004 Error with WorksheetFunction.VLookup?
  4. Create a new sheet for each unique agent and move all data to each sheet
  5. Set Auto Filtering multiple wildcards
  6. How to make Excel VBA variables available to multiple macros?
  7. Use of symbol # (hash) in VBA Macro
  8. Excel VBA: Can’t get a match, error “Unable to get the Match property of the WorksheetFunction class”
  9. Excel VBA looping through multiple worksheets
  10. When one should use Set [e.g for SpecialCells return value]?
  11. Excel: macro to export worksheet as CSV file without leaving my current Excel sheet
  12. Is there a faster CountIF
  13. VBA EXCEL To Prompt User Response to Select Folder and Return the Path as String Variable [duplicate]
  14. Excel VBA How to detect if something was pasted in a Worksheet
  15. Should I turn .CutCopyMode back on before exiting my sub procedure?
  16. Getting error Procedure too large in VBA Macros (Excel)
  17. How to add data validation to a cell using VBA
  18. How to do late binding in VBA?
  19. How to break long string to multiple lines
  20. How to copy a chart from Excel to PowerPoint?
  21. vba paste not working
  22. mac excel vba loop : from list & then export as pdf
  23. Can Advanced Filter criteria be in the VBA rather than a range?
  24. In Excel VBA, what is the way to check if the web page is fully loaded?
  25. Excel Useform: How to hide application but have icon in the taskbar
  26. Reading Web Pages using Excel VBA
  27. Excel VBA: auto click and open file from website
  28. How do I get a list of unique values from a range in Excel VBA?
  29. How to get selected value in multicolumn listbox
  30. Query my excel worksheet with VBA

Leave a Comment