PDA

View Full Version : Solved: Replace tilde character fails



YellowLabPro
09-12-2007, 05:49 AM
This bit of code is to find either a ~C space or a ~P space and replace it w/ nothing.
The syntax is accepted, but the replace is failing to recognize the string and not performing the instructions. I also tried it w/ one "~" character as I am aware Excel needs this character to find special characters.

either way:

Range("I6:I" & lrwTarget).Value = Range("Y6:Y" & lrwTarget).Value
WsT.Range("I6:I" & Range("Y" & Rows.Count).End(xlUp).Row).Select
Selection.Replace What:="~~? ", Replacement:=""

or

Range("I6:I" & lrwTarget).Value = Range("Y6:Y" & lrwTarget).Value
WsT.Range("I6:I" & Range("Y" & Rows.Count).End(xlUp).Row).Replace What:="~~? ", Replacement:=""

An example of the string I am working w/ is:

~C BURTON 08 S. WHITE ASYM SNBJ RSL LG

Bob Phillips
09-12-2007, 05:54 AM
Doug,

because you have used ~~?space, the string it will replace is one tilde, any character, and a space ... nothing else.

~9<space>works fine with your code, and so on.

rory
09-12-2007, 05:54 AM
Works fine for me. Does it help if you specify additional parameters:
Selection.Replace What:="~~? ", Replacement:="", lookat:=xlpart, matchcase:=false

YellowLabPro
09-12-2007, 05:57 AM
It was the xlpart.
I did a search in the range w/ excel's find.
The box to match entire contents was selected. This was selected on a search once and now I cannot get the default setting to keep that box unchecked. But anyway this clued me into look at xlpart.

YellowLabPro
09-12-2007, 05:59 AM
Thanks Rory and Bob,
It did not occur to me to go to excel and do it until after I posted.
I think I need an eye chart, w/ a Big E- and following the E are specific steps to follow before posting...
1. Go to excel and performt the steps manually and see the results.
2. Use the macro recorder.
3. When all else fails reboot.
4. Feel free to add to my list.

rory
09-12-2007, 06:09 AM
Find and Replace remember the settings between uses, so it is always better to set them explicitly. (though this is definitely a case of "do as I say, not as I do" as I am usually too lazy)

YellowLabPro
09-12-2007, 06:13 AM
lol.... yes.
One thing that if anyone has an idea- my find/replace w/in Excel as I mentioned earlier- on certain columns retains certain settings and on others is the correct default. For instance, the checkmark is in the Match Contents, if I deselect this setting and close the find/replace box the setting stays as it was when I reopen it. But when I close Excel and reopen it, the checkmark is back for this column, but not for the other columns.

rory
09-12-2007, 06:28 AM
I can't replicate that behaviour in XL2002. Which version do you use?

YellowLabPro
09-12-2007, 07:27 AM
2003.
Rory, It seems to be very erratic. Wondering if there is a setting in the registry or somewhere in the program that would reset certain defaults.

rory
09-12-2007, 07:54 AM
For it to vary from column to column, it would have to be stored in the workbook realistically.