selection.replace is doing weird things
Posted by Pasi Vartiainen on June 09, 2000 4:26 AM
I have part of macrocode, which is doing something really stupid. I have workbook, which have rabdom number of sheet, macro goes trough all sheets, and replaces certain things. There is typecode, which is in format "3AFE********", so I have 8 digits after that 3AFE. When I replace 3AFE with empty, I keep running on problems. All cells in sheets are formated to be in TEXT format, but despite that, every time wwhen I run my macro which delete's that 3AFE, same problem occures: If typecode is 3AFE0*******, then first zero is drop out. After that, typecode is ruined, becourse it must have 8 chars.
Is this excel's bug, or am I doing something wrong? Actual replacement code is:
Selection.Replace What:="3AFE", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
If it is excel's bug, I must find different solution.
Thank's for your answers
Pasi Vartiainen
Posted by JAF on June 12, 0100 6:47 AM
Selection.Replace What:="3AFE", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Pasi
There may be a better way of doing this, but the following will work.
Use your existing macro but make the following change: instead of having Replacement:="" use Replacement:="'"
This puts a text qualifier in front of the 8 digits and retains the zero.
JAF
Posted by JAF on June 12, 0100 6:50 AM
Oops - that isn't very clear.
What you need to do instead of just having the 2 double quotes (which replaces with nothing" you need to type the following: open double quote, SINGLE QUOTE (the text qualifier), close double quote.
If you copy the text in the previous message and paste it into your macro, it shows more clearly in the VBA Editor.
