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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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

M
  • Question
Vba code to find and replace in excel closed workbooks.
Replies
1
Views
348
6StringJazzer
6StringJazzer
Knockoutpie
  • Solved
If column BI contains "N" then replace values in the row from column G:BE using the cell in column BF as find criteria and BH as replacement criteria
Replies
2
Views
198
Knockoutpie
Knockoutpie
S
  • Solved
VBA Code to select a variable folder path, find multiple .xls then copy same named worksheets into new workbook.
Replies
4
Views
433
SteveD01
S
JeffGrant
  • Solved
VBA Code used twice, keeps table formatting on one sheet, but not on the other
Replies
4
Views
242
smozgur
smozgur
S
  • Question
VBA to ignore certain MAC Address values but process all others
Replies
1
Views
193
myall_blues
myall_blues

Forum statistics

Threads
1,215,004
Messages
6,122,659
Members
449,091
Latest member
peppernaut

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