Find and Replace

This method is only applicable to individual worksheets.
If you want to replace in the whole workbook you need to loop through all the worksheets.
The macro recorder will record the same thing regardless of whether you select sheet or workbook


Find Method

Finds information in particular range
Returns a range that represents the first cell where the information is found.
Returns Nothing if no matches can be found.
This is searching the active worksheet

Cells.Find What:="test to find" 
      Replacement:="replace it with this"
      LookIn:=xlValues
      LookAt:=xlLookAt.xlPart
      SearchOrder:=xlSearchOrder.xlByRows
      SearchDirection:=xlSearchDirection.xlNext
      MatchCase:=False
      MatchByte:=
      SearchFormat:=

What - The text you want to search for.
Replacement / After - A single cell to start the search from. The default is the top left cell of the range.
LookIn - This settings is saved each time you use this method. Allowing you to call the method again without specifying the value.
LookAt - This settings is saved each time you use this method. Allowing you to call the method again without specifying the value.
SearchOrder - This settings is saved each time you use this method. Allowing you to call the method again without specifying the value.
SearchDirection - This settings is saved each time you use this method. Allowing you to call the method again without specifying the value.
MatchCase - The default is False. Allows you to make the search case sensitive
MatchByte - The default is False. Allows you to only match double-byte characters.
SearchFormat - Allows the search to respect the FindFormat options. These options can be changed from Application.FindFormat.


FindNext

Can be used to repeat the search.
When the search completes it loops back to the start again
To avoid a continous loop you must compare the first cell with the cell returned.


FindPrevious

Can be used to repeat the search.



Replace Method


Dim objRange As Range 

Set objRange = Range("A1:D20")
objRange.Replace What:="test to find"
      Replacement:="replace it with this"
      LookAt:=xlLookAt.xlPart
      SearchOrder:=xlSearchOrder.xlByRows
      SearchDirection:=xlSearchDirection.xlNext
      MatchCase:=False

What - The text you want to search for.
Replacement - The text you want to replace it with.
LookAt - This setting is saved each time you use this method. Allowing you to call the method again without specifying the value.
SearchOrder - This settings is saved each time you use this method. Allowing you to call the method again without specifying the value.
SearchDirection - This settings is saved each time you use this method. Allowing you to call the method again without specifying the value.
MatchCase - The default is False. Allows you to make the search case sensitive
MatchByte - The default is False. Allows you to only match double-byte characters.
SearchFormat - Allows the search to respect the FindFormat options. These options can be changed from Application.FindFormat.


Dim objRange As Range 

   objFind = objRange.Find("some text", MatchCase:=False)

   objFind.Address

   objRange.FindNext(objFind)


Find and Replace Equal Signs

This is the equivalent of updating all the formulas

Cells.Replace What:="=" 
          Replacement:="="
          LookAt:=xlLookAt.xlWhole

You can use the following commands to reset these dialog boxes back to their default values

Application.FindFormat.Clear 
Application.ReplaceFormat.Clear

Replacing @

Sub Testing() 
Dim wsh As Excel.Worksheet
Dim rgeSelection As Excel.Range

    Set wsh = ActiveSheet
    Set rgeSelection = Application.Selection
    
    rgeSelection.Replace _
    What:="@", _
    Replacement:="", _
    LookAt:=XlLookAt.xlPart, _
    SearchOrder:=XlSearchOrder.xlByRows, _
    MatchCase:=False, _
    MatchByte:=False, _
    SearchFormat:=False, _
    ReplaceFormat:=False, _
    FormulaVersion:=xlReplaceFormula2
End Sub


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext