Find and Replace Loop in VBA

Question

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

Replace every instance of the word apple in this sheet with "banana"
print results (simple copy and paste)
Replace banana with "cats"
print results (simple copy and paste)
Replace "cats" with "dogs"
print results (simple copy and paste)
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

SJR · Accepted Answer · 2020-10-29 17:35:27Z

1

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

answered Oct 29, 2020 at 17:35

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MatthewHagemann Over a year ago

Could also use a 2-dimension array instead of 2 separate arrays

ERC Over a year ago

Hey SJR, thank you so much for your response! This will work! If I wanted to offset the print (copy and pate) by a row each time i.e. E13 for banana, E14 for cats... and then for the restore to base case there is no print, how would I go about this.

SJR Over a year ago

My pleasure. You could use Worksheets("Results").Range("E13").offset(i).PasteSpecial xlPasteValues.

ERC Over a year ago

I just tried the above approach and created a new sub to restore the base case without print (i.e. change dogs to apple without a copy and paste command) - Thank you for your help!

Collectives™ on Stack Overflow

Find and Replace Loop in VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related