VBA Syntax to Use Specific Cell Value in Worksheet 1 to Find & Replace in the other worksheets in the workbook

B

bearwires

Board Regular
Joined
Mar 25, 2008
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Hi, I am hoping someone can help me out with this, I've been searching online but cant figure out how to do it.

I need some help with VBA syntax for a macro button to do the following:

Find all instances of the word "Main Contractor" across an array of 12 worksheets (MS001.......MS012) & Replace all instances with the text written in cell B4 in a separate Worksheet (Project Input).
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
This little bit of code should work...
VBA Code: 
Sub bearwires()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.UsedRange.Replace What:="Main Contractor", Replacement:=Sheets("Project Input").Range("B4").Value, LookAt:=xlWhole
Next ws
End Sub
 
Upvote 0
Z51 said:
This little bit of code should work...
VBA Code: 
Sub bearwires()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.UsedRange.Replace What:="Main Contractor", Replacement:=Sheets("Project Input").Range("B4").Value, LookAt:=xlWhole
Next ws
End Sub
Click to expand...
Thanks for the response but it doesnt work for some reason.
Also, I have 15 worksheets in the workbook and I only want the find/replace to work on 12 of them.

The names of the worksheets are "MS001", "MS002", "MS003", "MS004", "MS005", "MS006", "MS007", "MS008", "MS009", "MS010", "MS011", "MS012"

Am I able to specify a worksheet array using this VBA code:

VBA Code: 
Sub bearwires()
sheetlist = Array("MS001","MS002", "MS003", "MS004", "MS005", "MS006", "MS007", "MS008", "MS009", "MS010", "MS011", "MS012")
For i = LBound(sheetlist) To UBound(sheetlist)
Worksheets(sheetlist(i)).Activate

Then perform the find/replace on each of these worksheets in the array only?

What VBA code would I need to include after this to find all instances of "Main Contractor" and replace with the text string value in Cell "B4" from the "Project Input" worksheet?

Thanks
 
Upvote 0
Ok, I have managed to get this to work by luck but the code looks horrendous and repetative.
Can someone help me out and reduce this code into something that looks nicer and more concise?

Thanks

VBA Code: 
Sub FindReplaceMainContractor()

Dim Findtext As String
Dim Replacetext As String

Findtext = "Main Contractor"
Replacetext = Sheets("Project Input Tab").Range("B4").Value
Sheets("MS001").Select

Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Findtext = "Main Contractor"
Replacetext = Sheets("Project Input Tab").Range("B4").Value
Sheets("MS002").Select

Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Findtext = "Main Contractor"
Replacetext = Sheets("Project Input Tab").Range("B4").Value
Sheets("MS003").Select

Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Findtext = "Main Contractor"
Replacetext = Sheets("Project Input Tab").Range("B4").Value
Sheets("MS004").Select

Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Findtext = "Main Contractor"
Replacetext = Sheets("Project Input Tab").Range("B4").Value
Sheets("MS005").Select

Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Findtext = "Main Contractor"
Replacetext = Sheets("Project Input Tab").Range("B4").Value
Sheets("MS006").Select

Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Findtext = "Main Contractor"
Replacetext = Sheets("Project Input Tab").Range("B4").Value
Sheets("MS007").Select

Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Findtext = "Main Contractor"
Replacetext = Sheets("Project Input Tab").Range("B4").Value
Sheets("MS008").Select

Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Findtext = "Main Contractor"
Replacetext = Sheets("Project Input Tab").Range("B4").Value
Sheets("MS009").Select

Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Findtext = "Main Contractor"
Replacetext = Sheets("Project Input Tab").Range("B4").Value
Sheets("MS010").Select

Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Findtext = "Main Contractor"
Replacetext = Sheets("Project Input Tab").Range("B4").Value
Sheets("MS011").Select

Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Findtext = "Main Contractor"
Replacetext = Sheets("Project Input Tab").Range("B4").Value
Sheets("MS012").Select

Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub
 
Upvote 0
How about:

VBA Code: 
Sub FindReplaceMainContractor()
'
    Dim i           As Long
    Dim Findtext    As String, Replacetext As String
    Dim sheetlist   As Variant
'
    Findtext = "Main Contractor"
    Replacetext = Sheets("Project Input Tab").Range("B4").Value
    sheetlist = Array("MS001", "MS002", "MS003", "MS004", "MS005", "MS006", "MS007", "MS008", "MS009", "MS010", "MS011", "MS012")
'
'----------------------------------------------------------------------------------
'
    For i = LBound(sheetlist) To UBound(sheetlist)
        Worksheets(sheetlist(i)).Activate
'
        Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, SearchOrder:=xlByRows, _
                MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next
End Sub
 
Upvote 0
johnnyL said:
How about:

VBA Code: 
Sub FindReplaceMainContractor()
'
    Dim i           As Long
    Dim Findtext    As String, Replacetext As String
    Dim sheetlist   As Variant
'
    Findtext = "Main Contractor"
    Replacetext = Sheets("Project Input Tab").Range("B4").Value
    sheetlist = Array("MS001", "MS002", "MS003", "MS004", "MS005", "MS006", "MS007", "MS008", "MS009", "MS010", "MS011", "MS012")
'
'----------------------------------------------------------------------------------
'
    For i = LBound(sheetlist) To UBound(sheetlist)
        Worksheets(sheetlist(i)).Activate
'
        Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, SearchOrder:=xlByRows, _
                MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next
End Sub
Click to expand...
This works great, thanks Johnny 😀
 
Upvote 0
johnnyL said:
Glad we could help.
Click to expand...
Hi Johnny, how would I modify this VBA code to find & replace 2 placeholders?
I tried just adding an additonal line with the same syntax but it only finds & replaces 1 of the placeholders.

e.g

Find "Weekday-Noise" on Worksheet "RA" & Replace with "Project Input Tab, cell B36 value"

Then,

Find "Weekend-Noise" on Worksheet "RA" & Replace with "Project Input Tab, cell B37 value"


I would also like to reverse the action as well with a separate macro button which will revert back to the placeholders, Weekday-Noise & Weekend-Noise
 
Upvote 0
VBA Code: 
    Dim Findtext2   As String, Replacetext2 As String
    Dim Findtext3   As String, Replacetext3 As String
'
    Findtext2 = "Weekday-Noise"
    Replacetext2 = Sheets("Project Input Tab").Range("B36").Value
'
    Findtext3 = "Weekend-Noise"
    Replacetext3 = Sheets("Project Input Tab").Range("B37").Value
'
    Sheets("RA").Cells.Replace What:=Findtext2, Replacement:=Replacetext2, LookAt:=xlPart, SearchOrder:=xlByRows, _
            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
'
    Sheets("RA").Cells.Replace What:=Findtext3, Replacement:=Replacetext3, LookAt:=xlPart, SearchOrder:=xlByRows, _
            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
 
Upvote 0
Solution
johnnyL said:
VBA Code: 
    Dim Findtext2   As String, Replacetext2 As String
    Dim Findtext3   As String, Replacetext3 As String
'
    Findtext2 = "Weekday-Noise"
    Replacetext2 = Sheets("Project Input Tab").Range("B36").Value
'
    Findtext3 = "Weekend-Noise"
    Replacetext3 = Sheets("Project Input Tab").Range("B37").Value
'
    Sheets("RA").Cells.Replace What:=Findtext2, Replacement:=Replacetext2, LookAt:=xlPart, SearchOrder:=xlByRows, _
            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
'
    Sheets("RA").Cells.Replace What:=Findtext3, Replacement:=Replacetext3, LookAt:=xlPart, SearchOrder:=xlByRows, _
            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Click to expand...
Thanks Johnny, works great :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
VBA - Find Replace on selected cells
Replies
3
Views
408
rsulliva
R
L
  • Solved
VBA Replace All '/' in a sheet, except for formulas
Replies
4
Views
180
MARK858
MARK858
R
  • Question Question
How to find worksheet that contains specific table
Replies
2
Views
111
radek
R
W
  • Solved
vba/macro to Find a line on another sheet and replace information in just 1 cell in that line
Replies
5
Views
95
mumps
mumps
S
  • Solved
Find and replace multiple values with other values in a range
Replies
8
Views
456
Rick Rothstein
Rick Rothstein

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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