Automating Find & Replace

Sub Macro1()
Call FindandReplace("Pepsi", "Coke")
End Sub
Private Sub FindandReplace(CompanyA As String, CompanyB As String)
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
    Cells.Replace What:=CompanyA, Replacement:=CompanyB, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
Next Ws

End Sub

This type of code might give you a starting point to build code.

Thanks for the response. From the looks of this code it might not be deep enough and I need it to be much more global so the sheet stays dynamic if I were to add a new <code> from column A to automate across the sheets. Right now I have about 100 <code>s that I need to correlate a (replace) with.

So I decided to split up the Mastersheet from the MasterTemplate for ease of programming. In addition, on the Mastersheet I got rid of the empty rows because the Mastersheet is a table, and should be treated that way. There are ways to program around that, but I find that programming is much harder. In addition, Range Names act as arrays and the code is much easier to read.

Hello. Thanks for the files. But when I download both and try to hit "replace" on the MASTERSHEET I get the following error. both files are saved on my Desktop.

Screen Shot 2014-05-05 at 12.11.32 PM.jpg

PharmerGuy,
Why you don't use names!
For example in sheet "Code Sheet" cell D2 is named "Company_address" then in sheet "Scope" in cell C8 put = Company_address
PCI

Originally Posted by PCI

PharmerGuy,
Why you don't use names!
For example in sheet "Code Sheet" cell D2 is named "Company_address" then in sheet "Scope" in cell C8 put = Company_address
PCI

That would be simplest but there is multiple text in one cell. The replacement function is usually a set of words within a larger paragraph.

OK and I suppose you know you can concatenate strings like:
= Project_Spensor & " wants CRM system to support both internal and external sales efforts – track clients, sales pipeline, probability of close, amount of contract, etc."
Where Project_Sponsor stands for cell D7 in sheet "Code Sheet"
Just to close the story with names

Try next code and check, check, check.
Has been remove the space after the comma when 2 sheets where mentioned (Sheet1,Sheet2) to avoid confusion with the space inside the sheet's name

Option Explicit

Sub ReplaceData()
Dim ObjDic   As Object
Dim WkRg  As Range
Dim F   As Range
Dim G
Dim WkSh As Worksheet
Dim WkStg1 As String, WkStg2 As String
Dim WkSet
Dim I  As Integer
Dim AAA, BBB, CCC
    Application.ScreenUpdating = False
    Set ObjDic = CreateObject("Scripting.Dictionary")
    With Sheets("Code Sheet")
        For Each F In Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
            If (F.Value <> "") Then ObjDic(F.Value) = F.Offset(0, 1).Value
        Next F
        For Each F In Range(.Range("E2"), .Range("E" & Rows.Count).End(xlUp))
            If (F.Value <> "") Then
                If (F.Value = "All") Then
                    For Each WkSh In Worksheets
                        If (WkSh.Name <> "Code Sheet") Then
                            For Each G In ObjDic.keys
                                WkStg1 = G
                                Call FindReplace(WkStg1, ObjDic(G), WkSh.Name)
                            Next G
                        End If
                    Next WkSh
                Else
                    WkSet = Split(F.Value, ",")
                    For I = 0 To UBound(WkSet, 1)
                        For Each G In ObjDic.keys
                            WkStg1 = G: WkStg2 = WkSet(I)
                            Call FindReplace(WkStg1, ObjDic(G), WkStg2)
                        Next G
                    Next I
                End If
            End If
        Next F
    End With
    Application.ScreenUpdating = True
End Sub

Sub FindReplace(StgOrg As String, StgDest As String, WkSh As String)
    With Sheets(WkSh).Cells
        .Replace What:=StgOrg, Replacement:=StgDest, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
End Sub

Hi, the reason that you are having issue with that line of code is that the Template workbook is opened by the masterworkbook. If it starts closed than it will run.

Thanks,

Gregg

Originally Posted by greggpetersen75

Hi, the reason that you are having issue with that line of code is that the Template workbook is opened by the masterworkbook. If it starts closed than it will run.

Thanks,

Gregg

Hey Greg!

I have both files in the same folder. When I open the MasterSheet and click on "replace code" I get a DEBUG error. It highlights that syntax I previously mentioned. The Project_Master_Template file is not open at this time.

You should not be getting an error if the MasterSheet.xlsm is open and Project_Master_Template.xlsx is closed and they are in the same folder. Did you change the name of the Project_Master_Template.xlsx in anyway?

Thanks,

Gregg

Originally Posted by greggpetersen75

You should not be getting an error if the MasterSheet.xlsm is open and Project_Master_Template.xlsx is closed and they are in the same folder. Did you change the name of the Project_Master_Template.xlsx in anyway?

Thanks,

Gregg

Hey Gregg,

Sorry but I did not change a thing. When I click on the replace button I get this error. Note in the picture both files are on my Desktop and downloaded directly from this site as-is.

Screen Shot 2014-05-07 at 10.31.23 AM.jpg

If I click debug it takes me to the syntax line:

Set TemplateWorkbook = Workbooks.Open(Filename:=ThisWbk.Path & "\Project_Master_Template.xlsx")

PharmerGuy,
Did you ever try the macro I sent, just for the fun and my understanding please comment.
PCI

You are running a Mac, I am sure that is what the issue is... I will come up with another solution when I return.

Originally Posted by greggpetersen75

You are running a Mac, I am sure that is what the issue is... I will come up with another solution when I return.

Good call, Gregg; common issue I'm told. And unfortunate.

Did you see my solution on page 2 of this thread?

Originally Posted by greggpetersen75

Did you see my solution on page 2 of this thread?

Hey Gregg,

Still getting the error. I even tried to change the path so it read xlsm but no luck either way. I'm going to install Excel under Parallels tonight and get back to you to see if it works under that environment. Thank you for your hard work either way.

No I built a workbook where all the data stays in that workbook.

Here you go. Only thing that I "Hard coded" was that the worksheet name "Code Sheet" should keep that name. Also, I still would keep the "Code Sheet" in a table format with out any blank rows.

Thanks,

Gregg