CODEWITHSUNDEEP
vbaloopsreplacefind
ERC
ERC

Reputation: 78

Find and Replace Loop in VBA

I have a sheet with formulas containing the text "apple". I want to do the following:

  1. Replace every instance of the word apple in this sheet with "banana"
  2. print results (simple copy and paste)
  3. Replace banana with "cats"
  4. print results (simple copy and paste)
  5. Replace "cats" with "dogs"
  6. print results (simple copy and paste)
  7. Finally end with restoring base case i.e. replace "dogs" with apple"

I've got the first iteration working (see below) just need help on how to implement the loop.

Thanks in advance!!

Sub FindReplaceAll()

Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant

fnd = "apple"
rplc = "banana"

'Store a specfic sheet to a variable
  Set sht = Sheets("Trace")

'Perform the Find/Replace All
  sht.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    
Range("ResFinal").Copy
    Worksheets("Results").Activate
    Range("E13").PasteSpecial xlPasteValues
   
End Sub

Upvotes: 0

Views: 849

Answers (1)

SJR
SJR

Reputation: 23081

You could put the items into two arrays and loop through them, viz

Sub FindReplaceAll()

Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant
Dim i As Long

fnd = Array("apple", "banana", "cats", "dogs")
rplc = Array("banana", "cats", "dogs", "apple")

'Store a specfic sheet to a variable
Set sht = Sheets("Trace")

'Perform the Find/Replace All
For i = LBound(fnd) To UBound(fnd)
    sht.Cells.Replace what:=fnd(i), Replacement:=rplc(i), _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False
    Range("ResFinal").Copy
    Worksheets("Results").Range("E13").PasteSpecial xlPasteValues
Next i

End Sub

Upvotes: 1

Related Questions