Find and Replace Macro for Excel (If x exists then replace y with x) - For Microsoft Excel

[churchizactiive]

I created a macro in excel that runs a specific report for me and it works beautifully. I want to upgrade this macro to replace the "Insert Association Name" with the name of the specific community. That community name can be found in every report in the details column. For this one in particular it is named Apple Tree as described in the details column.

Essentially I'm looking for this new macro to see if "x community" exists then replace Insert Association Name with "x community". I would like the macro to run the instance for every community in my portfolio just incase when i run this report it's a different community name located within the details column

It would be an added bonus for the macro to include a print to pdf and save under a specific name too.

FYI - The Insert Association Name is in the Header so I don't know if that will make a difference. I tried the find and replace normally to replace Insert Association Name with Apple Tree and it didn't work.

 

[HaHoBe]

Hi,

maybe this can do for a start:

Public Sub MrE_1227933_170181C()
' https://www.mrexcel.com/board/threads/find-and-replace-macro-for-excel-if-x-exists-then-replace-y-with-x-for-microsoft-excel.1227933/
' Static concept, only DataRange in picture - no xl2BB
  Dim sReplacment     As String
  
  Const cstrWHAT      As String = "Insert Association Name"
  Const cstrCOL       As String = "F"
  
  With ActiveSheet
    'address to be chamged to suit
    sReplacment = .Cells(4, cstrCOL).Value
    
    With .PageSetup
      ' Substitute Header/Footer values
      .LeftHeader = Application.WorksheetFunction.Substitute( _
                    .LeftHeader, cstrWHAT, sReplacment)
      .CenterHeader = Application.WorksheetFunction.Substitute( _
                      .CenterHeader, cstrWHAT, sReplacment)
      .RightHeader = Application.WorksheetFunction.Substitute( _
                     .RightHeader, cstrWHAT, sReplacment)
      .LeftFooter = Application.WorksheetFunction.Substitute( _
                    .LeftFooter, cstrWHAT, sReplacment)
      .CenterFooter = Application.WorksheetFunction.Substitute( _
                      .CenterFooter, cstrWHAT, sReplacment)
      .RightFooter = Application.WorksheetFunction.Substitute( _
                     .RightFooter, cstrWHAT, sReplacment)
    End With
  End With
End Sub

Are you talking about one sheet, a couple of sheets, a workbook to save? And where: at a given place, in folder of workbook with code, any folder to be chosen by user?

Ciao,
Holger