Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Double Conditional Formatting | Excel Discussion (Misc queries) | |||
double axis, double problem (i hope only to me) | Charts and Charting in Excel | |||
Cell double-clicking problem | Excel Discussion (Misc queries) | |||
Property Let: assign return value of Double when passing String | Excel Discussion (Misc queries) | |||
Double entry problem | Excel Discussion (Misc queries) |