Solved: Replace tilde character fails

[YellowLabPro]

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:
[vba]
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:=""
[/vba]
or
[vba]
Range("I6:I" & lrwTarget).Value = Range("Y6:Y" & lrwTarget).Value
WsT.Range("I6:I" & Range("Y" & Rows.Count).End(xlUp).Row).Replace What:="~~? ", Replacement:=""
[/vba]
An example of the string I am working w/ is:

~C BURTON 08 S. WHITE ASYM SNBJ RSL LG

[Bob Phillips]

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]

Works fine for me. Does it help if you specify additional parameters:
[VBA]Selection.Replace What:="~~? ", Replacement:="", lookat:=xlpart, matchcase:=false[/VBA]

[YellowLabPro]

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]

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]

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]

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]

I can't replicate that behaviour in XL2002. Which version do you use?

[YellowLabPro]

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]

For it to vary from column to column, it would have to be stored in the workbook realistically.