Find and Replace Macro to Omit a Specified Worksheet

matratus34

Board Regular
Joined
Nov 21, 2013
Messages
73
Hi - I've got a workbook with around 50 data tabs which are updated monthly and I need to do a find and replace on around 40 different values each time the data is refreshed. (the SPV values)

e.g
find ABC replace with XYZ
find DER replace with NG1
find FOR replace with ZG1
x40

I've got a tab with all my old and new values stored ("SPVList")

I've written a macro that will loop down my old and new values and find and replace on each tab but this also amends the values on my "SPVList" tab which I need to keep intact for the next time I run the macro.

Any help with the code so that it changes the values on all tabs apart from the "SPVList" tab would be greatly appreciated.

Sub SPV_Changes()


'Changes SPV code to parent DOH code

Worksheets("SPVList").Select

'Ask if user want to change the DOH codes - if not exit macro

chgSPV = MsgBox("Do you want to amend SPV Codes?", vbYesNo)

If chgSPV = vbNo Then Exit Sub

'Loop through rows containing SPV codes and replace with parent codes

i = 6

Do While Not IsEmpty(Cells(i, 2))


Selection.Replace What:=Cells(i, 2).Value, Replacement:=Cells(i, 3).Value, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

i = i + 1

Loop

End Sub


Thanks Guys
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,052
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub matratus()
   Dim Ary As Variant
   Dim Ws As Worksheet
   Dim i As Long
   
   With Sheets("SPVlist")
      Ary = .Range("B6:C" & .Range("B" & Rows.Count).End(xlUp).Row)
   End With
   For Each Ws In WorkSheets
      If Not Ws.Name = "SPVList" Then
         For i = 0 To UBound(Ary)
            Ws.UsedRange.Replace Ary(i, 1), Ary(i, 2), xlWhole, , False, , False, False
         Next i
      End If
   Next Ws
End Sub
 

matratus34

Board Regular
Joined
Nov 21, 2013
Messages
73
Hi Fluff - that's brilliant - Thank you so much.

Just one other thing - if there was more than one worksheet that I didn't want to amend how would I do that?

Would it be amending this line?

If Not Ws.Name = "SPVList" Then

would I just need to add an OR?

For example:

If Not Ws.Name = "SPVList" or "Sheet2" or "Sheet3" Then


Thanks again
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,052
Office Version
  1. 365
Platform
  1. Windows
To ignore multiple sheets use
VBA Code:
Sub matratus()
   Dim Ary As Variant
   Dim Ws As Worksheet
   Dim i As Long
   
   With Sheets("SPVlist")
      Ary = .Range("B6:C" & .Range("B" & Rows.Count).End(xlUp).Row)
   End With
   For Each Ws In WorkSheets
      Select Case Ws.Name
         Case "SPVList", "Sheet2", "Sheet3"
         Case Else
            For i = 0 To UBound(Ary)
               Ws.UsedRange.Replace Ary(i, 1), Ary(i, 2), xlWhole, , False, , False, False
            Next i
      End Select
   Next Ws
End Sub
 

matratus34

Board Regular
Joined
Nov 21, 2013
Messages
73

ADVERTISEMENT

Fantastic - much appreciated - Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,052
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

matratus34

Board Regular
Joined
Nov 21, 2013
Messages
73

ADVERTISEMENT

You're welcome & thanks for the feedback.

I just tested and got a Runtime 9 / Subscript out of Range error

I've changed:
For i = 0 To UBound(Ary)
to

For i = 1 To UBound(Ary)

and it runs - does that seem ok?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,052
Office Version
  1. 365
Platform
  1. Windows
Yes that's fine, it was a mistake on my part.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,052
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,144,523
Messages
5,724,836
Members
422,582
Latest member
Vaibhav Wadhwa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top