String || How to do selection.replace with space?

[labo2002]

Hello all.

I would like to seek your expertise on this. My code is supposed to replace the word "Template" in my formulas and replace it with "Staff 1". I can't get it to work.
Below is my code. Any help will be greatly appreciated.

Selection.Replace What:="Template", Replacement:="Staff 1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

[Paul_Hossler]

If 'Template' is text in a cell, and I select the range of cells, it works for me

Capture.JPG


Do you mean that "Template" is part of a formula?

[jolivanes]

Does this work?

Selection.Formula = Replace(Selection.Formula,"Template", "Staff 1")

If it is needed for a larger range, let us know.

[labo2002]

Yes, it is part of the larger range. Basically, the code will highlight the current region, replace the word "Template" inside the formula of all the highlighted cells to "Staff 1".

Selection.CurrentRegion.Select
Selection.Replace What:="Template", Replacement:="Staff 1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("ForUpload").Select
Range("A1").Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

[labo2002]

Yes. The template is part of the formula.

[labo2002]

Does this work?

Selection.Formula = Replace(Selection.Formula,"Template", "Staff 1")

If it is needed for a larger range, let us know.

Yes, it is part of the larger range. Basically, the code will highlight the current region, replace the word "Template" inside the formula of all the highlighted cells to "Staff 1". Below is the complete code.

Selection.CurrentRegion.Select
Selection.Replace What:="Template", Replacement:="Staff 1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("ForUpload").Select
Range("A1").Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

[jolivanes]

Have not tried it. See if it works.
Change Range reference as required.

Sub from_jindon()
    [A2:A500].Replace "Template", "Staff 1", xlPart
End Sub

[labo2002]

Have not tried it. See if it works.
Change Range reference as required.

Sub from_jindon()
    [A2:A500].Replace "Template", "Staff 1", xlPart
End Sub

I tried it. It doesn't work as it is asking for the mapping.
My formula looks like this;

=Template!$A$3

Basically, i have a worksheet named Staff 1. In my macro sheet, I need to replace the template with Staff 1 so it will populate using the data from Staff 1 worksheet.

[Paul_Hossler]

You didn't say that it was a WS name … with a space in it

If I put single quotes around the 'Staff 1' it works

Sub test()
    Selection.Replace What:="Template", Replacement:="'Staff 1'", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

[labo2002]

That's great. I was able to proceed with the code. Now just to clean up the extra quotes in my final report.

Thank you for your help guys. I really appreciate it.

You didn't say that it was a WS name … with a space in it

If I put single quotes around the 'Staff 1' it works

Sub test()
    Selection.Replace What:="Template", Replacement:="'Staff 1'", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub