Duplicate find and replace operation using for loop

[xerxes1]

The aim of the code is to take a template (“Original”) and perform a “find/replace” on it for a number of inputs which are set out in a table (in “CaseMatrix”). The code then duplicates the template in new tabs based on the number of rows in the table. i.e. 5 rows = 5 new tabs based on the template.

At the moment there are 3 things I want found/replaced: items in column B, C and D. But at some point I will want 5, or 10, or 25 things found/replaced, and will need to add more columns.

The code I have at the moment has 3 distinct sets of statements to perform the “find/replace” , one for each item in columns B, C and D. How can I simplify this so that if I wanted N columns, I wouldn’t have to duplicate the code N times. I’m not sure how to simplify the code or create the loop to do this. Any help would be appreciated.

Here is the code so far (using Excel VBA 2010):

Sub Backup()
Dim NoFiles As Integer
Set NoRange = Range("CaseMatrix!A6:A100")
NoFiles = Application.Max(NoRange)
'Make sure there are no spaces before an "="
ActiveWorkbook.Sheets("Original").Select
Cells.Replace What:=" =", Replacement:="=", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

For NoTimes = 1 To NoFiles
ActiveWorkbook.Sheets("Original").Copy , before:=ActiveWorkbook.Sheets("Original")
ActiveSheet.Name = NoTimes
Next
For i = 6 To (NoFiles + 5)
ActiveWorkbook.Sheets(i - 5).Select
Cells.Find(What:=Range("CaseMatrix!B2"), after:=Range("A1"), LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Cells.Find(What:=Range("CaseMatrix!B3"), after:=ActiveCell.Offset(-1, 0), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Cells.Find(What:=Range("CaseMatrix!B4"), after:=ActiveCell.Offset(-1, 0), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:=Range("CaseMatrix!B4") & "*,", _
Replacement:=Range("CaseMatrix!B4") & " = " & Range("CaseMatrix!B" & i) & Range("CaseMatrix!B5") & ",", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

ActiveWorkbook.Sheets(i - 5).Select
Cells.Find(What:=Range("CaseMatrix!C2"), after:=Range("A1"), LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Cells.Find(What:=Range("CaseMatrix!C3"), after:=ActiveCell.Offset(-1, 0), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Cells.Find(What:=Range("CaseMatrix!C4"), after:=ActiveCell.Offset(-1, 0), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:=Range("CaseMatrix!C4") & "=*,", _
Replacement:=Range("CaseMatrix!C4") & "= " & Range("CaseMatrix!C" & i) & Range("CaseMatrix!C5") & ",", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

ActiveWorkbook.Sheets(i - 5).Select
Cells.Find(What:=Range("CaseMatrix!D2"), after:=Range("A1"), LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Cells.Find(What:=Range("CaseMatrix!D3"), after:=ActiveCell.Offset(-1, 0), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Cells.Find(What:=Range("CaseMatrix!D4"), after:=ActiveCell.Offset(-1, 0), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:=Range("CaseMatrix!D4") & "*,", _
Replacement:=Range("CaseMatrix!D4") & " = " & Range("CaseMatrix!D" & i) & Range("CaseMatrix!D5") & ",", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
Next
End Sub

 

[CharlesChuckieCharles]

can't see the point of the B2 and B3 finds when B4 is set as the active cell ( same for C and D ) ?

 

[xerxes1]

In the "Original" template there may be multiple instances of the label B4, so this code will search for identifying labels given in cells B2 and B3 and perform the replace on the next instance that B4 occurs.

Hope this answers your question.