PDA

View Full Version : Replace function not working in script form.



WebGuy
05-28-2007, 05:30 AM
Hi !
I have been trying to replace "." with ",". The replacefunction which works fine when i use it manually does not work at all when i try it in script form.

This is what i have so far :

While ActiveCell.Value <> ""
ActiveCell.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
' Selection.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(1, 0).Select
Wend


I want the script to change 401.345 into 401,345. I have a column full of batchweights which all have to be changed. My script only changes a handfull of them, in the rest of the weights i am left with 401345 instead of 401,345 which i want.
It is as if the script forgets to add the comma.

Help !

Bob Phillips
05-28-2007, 05:52 AM
It worked fine for me, but you can do it without selecting



Range(ActiveCell, ActiveCell.End(xlDown)).Replace _
What:=".", _
Replacement:=",", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

mdmackillop
05-28-2007, 05:59 AM
You can show commas in a numeric cell by
1. Changing the format of the cell to #,000
2. Changing the cell to Text format and adding the commas.
If you run your code on the following, the first two become text values, the third is interpreted as a number
123.4
123.45
123.456
Why not multiply by 1,000 and reset your formatting?

WebGuy
05-29-2007, 02:07 AM
Hi thanks for your excellent tips!
Reading through them i realized that after replacing the "." with "," i get a mix of number and text elements. both "123456", "123,45", "123,4" and "123" depending on the weight measurement systems.
I managed to autoformat the "123456" numbers by deviding them with 1000, but i can't seem to convert the "132,45" text elements by multiplying them with 1.
If i do it manually it works fine, but when i select the whole range and multiply it by 1 in a script, they remain as text elements.

The data is as i said in one columna and the textelements are randomly occuring:

123,456
123,456
123,45
123,456
123,45
123,456
123,456
123,456
...


Here is my code:

ActiveCell.FormulaR1C1 = "1"
Selection.Copy
Range("E1:E60").Select
'Range("E60").Activate
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False

Bob Phillips
05-29-2007, 04:41 AM
Just replace the decimal point by nothing and format with commas.