PDA

View Full Version : [SOLVED] String || How to do selection.replace with space?



labo2002
07-19-2018, 05:57 PM
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
07-19-2018, 06:06 PM
If 'Template' is text in a cell, and I select the range of cells, it works for me

22595


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

jolivanes
07-19-2018, 08:30 PM
Does this work?

Selection.Formula = Replace(Selection.Formula,"Template", "Staff 1")
If it is needed for a larger range, let us know.

labo2002
07-19-2018, 09:13 PM
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
07-19-2018, 09:16 PM
Yes. The template is part of the formula.

labo2002
07-19-2018, 09:20 PM
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
07-19-2018, 10:07 PM
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
07-20-2018, 12:44 AM
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. :banghead:

Paul_Hossler
07-20-2018, 05:39 AM
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
07-24-2018, 09:15 PM
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