Replace loop to target sheet

Dom523647

New Member
Joined
Jul 31, 2020
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Hello,

Could you please help me to amend this VBA code which replaces a list of text/values throughout the entire workbook from a table. I need to remove the loop that cycles through all the worksheets and make reference to the sheet I want to target i.e. "sheet 2"

VBA code that

Sub Multi_FindReplace()

Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant

'Create variable to point to your table
Set tbl = Worksheets("Sheet1").ListObjects("Table1")

'Create an Array out of the Table's Data
Set TempArray = tbl.DataBodyRange
myArray = Application.Transpose(TempArray)

'Designate Columns for Find/Replace data
fndList = 1
rplcList = 2

'Loop through each item in Array lists
For x = LBound(myArray, 1) To UBound(myArray, 2)
'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
For Each sht In ActiveWorkbook.Worksheets
If sht.Name <> tbl.Parent.Name Then

sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

End If
Next sht
Next x

End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Assuming the rest of your code is ok (I haven't looked at it closely), replace the For Each sht- Next sht loop with this:
VBA Code:
Dim sht As Worksheet
Set sht = Sheets("Sheet2")  'change sheet name to suit
sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
 
Upvote 0
Assuming the rest of your code is ok (I haven't looked at it closely), replace the For Each sht- Next sht loop with this:
VBA Code:
Dim sht As Worksheet
Set sht = Sheets("Sheet2")  'change sheet name to suit
sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Thanks, it works perfectly! Just in case, I'd need to use the macro on multiple sheets, can I replace :
Set sht = Sheets("Sheet2") with Set sht = Sheets(Array("Sheet1", "Sheet3")) ?
 
Upvote 0
Thanks, it works perfectly! Just in case, I'd need to use the macro on multiple sheets, can I replace :
Set sht = Sheets("Sheet2") with Set sht = Sheets(Array("Sheet1", "Sheet3"))
?
You are welcome - thanks for the reply.
To answer your question, I'd use:
For Each sht in Worksheets
sht.cells.Replace ....
Next sht
 
Upvote 0
You are welcome - thanks for the reply.
To answer your question, I'd use:
For Each sht in Worksheets
sht.cells.Replace ....
Next sht

@JoeMo
I can't wrap my head around this code, could you please help? To avoid referencing multiple sheets I will change Sheets name manually in editor:

Dim sht As Worksheet
Set sht = Sheets("Sheet2") 'I will change this part manually to Sheet 3 and etc.
sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

However, if I'd need to Find/Replace data values only in Sheet 2 column B for example instead of whole worksheet, how the above code should be changed?
 
Upvote 0
Replace Cells with the range you want to do the find/replace on.

For example for column B it would look like this.
VBA Code:
sht.Range("B:B").Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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
Back
Top