Formatting problem - no longer works

[peterhw]

    Sheets("Print_Results").Range(myrange).Replace What:="£", Replacement:=""  '   , SearchOrder:=xlByColumns, MatchCase:=True
    Sheets("Print_Results").Range(myrange).Replace What:="$", Replacement:=""  '   , SearchOrder:=xlByColumns, MatchCase:=True

I have been using the above for ages (years) to enable me to take the contents of a listbox and print to a worksheet but to remove any 'hard' currency symbols before formatting that then allows me to see formatted numbers but without the 'hard' symbol. (they still appear as numbers rather than alpha strings with numbers, symbols, - sign etc). Each of the cells is displayed with top left hand corner containing the 'Green Triangle'

Earlier today it just failed to work and I can not see any obvious reasons. The Worksheet is correct the range is 'large'.
I get round the problem by replacing - i.e. REPLACE(xxxx,"£","") which does work
I have exited Excel and reloaded

Suspect I have done something, somewhere but just eludes me at the moment, (using Excel / Office 2016)
Anything suggestions appreciated

 

[Skiff]

Try changing currency values in What:= with character codes like Chr(63) (for pounds) nad Chr(36) for dollars

What:=Chr(63)

 

[Peter_SSs]

You need to specify 'LookAt'

Sheets("Print_Results").Range(myrange).Replace What:="£", Replacement:="", LookAt:=xlPart

 

[peterhw]

Really weird....
First I tried above using Chr(63) as per code below (without LOOAT line and lines UNCOMMENTED with BREAK at Range(myrange).select (to start reformatting).
Then all the date in the range got deleted - the listbox (with the source) was fine and all the data was printed to the Print Results sheet.
myrange contains A6:J5000 and rows 6 onwards vanished.
I then tried second suggestion including LookAt and all appeared to work fine.
Many thanks for both suggestions but just no idea what happened - I have been using this code for several years (without LookAt)
If I uncover any more I will post

    myrange = "A" + t1 + ":J" + t2
    'Chr(63) (for pounds) nad Chr(36) for dollars
'    Sheets("Print_Results").Range(myrange).Replace What:=Chr(63), Replacement:=""  '   , SearchOrder:=xlByColumns, MatchCase:=True
'    Sheets("Print_Results").Range(myrange).Replace What:=Chr(36), Replacement:=""  '   , SearchOrder:=xlByColumns, MatchCase:=True
    'Sheets("Print_Results").Range(myrange).Replace What:="%", Replacement:=""  '   , SearchOrder:=xlByColumns, MatchCase:=True
    Sheets("Print_Results").Range(myrange).Replace What:="£", Replacement:="", LookAt:=xlPart
    Range(myrange).Select
    With Selection

Thanks again for swift responses

 

[Peter_SSs]

Many thanks for both suggestions ...

Your welcome.

... but just no idea what happened

With Find/Replace of course there is that setting about whether to match the whole cell or just look at any part within the cell. Find/Replace remembers that setting from the previous time so before your code failed, at some point you have done a Find or Find/Replace (either manually or by code) where you used the setting 'Match entire cell contents' (manual) or LookAt:=xlWhole (code) and therefore that was the remembered setting.

Have a look here, especially the 'Remarks' section and remember to always specify those settings that get remembered if they are relevant to your current goal.

 

[peterhw]

Your welcome.

With Find/Replace of course there is that setting about whether to match the whole cell or just look at any part within the cell. Find/Replace remembers that setting from the previous time so before your code failed, at some point you have done a Find or Find/Replace (either manually or by code) where you used the setting 'Match entire cell contents' (manual) or LookAt:=xlWhole (code) and therefore that was the remembered setting.

Have a look here, especially the 'Remarks' section and remember to always specify those settings that get remembered if they are relevant to your current goal.

Many thanks. Will now headover and take a look at your suggested link.