Formatting Problem on Double-Zero String
 

If using xlWhole doesn't prevent this (which you say it doesn't) then try

Sub Tester1()
lFinalRow = 100
iFirstDataRow = 2
icol = 1
With Worksheets("sheet1")
..Range(.Cells(iFirstDataRow, icol), .Cells(lFinalRow, icol)).Replace _
What:="00", Replacement:="'00", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
..Range(.Cells(iFirstDataRow, icol), .Cells(lFinalRow, icol)).Replace _
What:="'00", Replacement:="00", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub

Which worked for me.

Regards,
Tom Ogilvy



"Tim Childs" wrote in message
...
Hi



I have a macro which does formatting on generic data downloaded from a
financial system.



I am looking for a way to stop the replacement of the cells in a
spreadsheet
containing "00" in special circumstances.



The data comes out of the financial systems as formulae including ="00"
and
is then paste-valued, leaving the "unstable" string 00. (Unstable because
if
you edit the cell and press return the contents revert to the number zero,
not unsurprisingly)



The first run of the macro uses this code to put the single quote (ASCII
39)
in front of the double zero string.

.Range(.Cells(iFirstDataRow, iCol), .Cells(lFinalRow, iCol)).Replace
What:="00", Replacement:="'00", LookAt:=xlWhole, _

SearchOrder:=xlByRows, MatchCase:=False





Sometimes the macro is run again and then everything else in it works fine
but it puts a second quote in front of the double zero string i.e. the
entry
has a visible quote, because there are two ASCII 39 characters, one of
which
becomes visible and eans the string is actually 3 characters long (if the
LEN function is used)



What is a neat method of avoiding this extra quote appearing, please?



Sorry the explanation is so long



Thanks



Tim

Formatting Problem on Double-Zero String
 

Tom

I said something neat and that fits the bill!

Now why couldn't I have thought of that <g

THANKS

Tim
"Tom Ogilvy" wrote in message
...
If using xlWhole doesn't prevent this (which you say it doesn't) then try

Sub Tester1()
lFinalRow = 100
iFirstDataRow = 2
icol = 1
With Worksheets("sheet1")
.Range(.Cells(iFirstDataRow, icol), .Cells(lFinalRow, icol)).Replace _
What:="00", Replacement:="'00", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.Range(.Cells(iFirstDataRow, icol), .Cells(lFinalRow, icol)).Replace _
What:="'00", Replacement:="00", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub

Which worked for me.

Regards,
Tom Ogilvy



"Tim Childs" wrote in message
...
Hi



I have a macro which does formatting on generic data downloaded from a
financial system.



I am looking for a way to stop the replacement of the cells in a
spreadsheet
containing "00" in special circumstances.



The data comes out of the financial systems as formulae including ="00"
and
is then paste-valued, leaving the "unstable" string 00. (Unstable
because
if
you edit the cell and press return the contents revert to the number
zero,
not unsurprisingly)



The first run of the macro uses this code to put the single quote (ASCII
39)
in front of the double zero string.

.Range(.Cells(iFirstDataRow, iCol), .Cells(lFinalRow, iCol)).Replace
What:="00", Replacement:="'00", LookAt:=xlWhole, _

SearchOrder:=xlByRows, MatchCase:=False





Sometimes the macro is run again and then everything else in it works
fine
but it puts a second quote in front of the double zero string i.e. the
entry
has a visible quote, because there are two ASCII 39 characters, one of
which
becomes visible and eans the string is actually 3 characters long (if
the
LEN function is used)



What is a neat method of avoiding this extra quote appearing, please?



Sorry the explanation is so long



Thanks



Tim