Seemingly impossible replacement task.

[WebGuy]

A week or two ago i got a few tips on how to replace "." with "," . After a whole lot of tedious coding and lots of hitting my head against the keyboard i finally got it right. The thing is that i had to fool excel into adding my commas by removing the "." and replacing it with "", then deviding the number by 10, 100 or 1000 to get the comma in the right place. see code below :

[VBA]
'** Format and replace "." with "" in Weight field.
Range("E1").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Replace _
What:=".", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
'** Division of weights with 10,100 or 1000 & convertion from text to number format.
'** Selection centered.
Range("E1").Select
While ActiveCell.Value <> ""
If ActiveCell.Value <= 999 Then
ActiveCell.Value = (ActiveCell.Value & ",0")
ActiveCell.Value = ActiveCell.Value * 1
ActiveCell.Offset(1, 0).Select
ElseIf (ActiveCell.Value > 999 And ActiveCell.Value <= 9999) Then
ActiveCell.Value = (ActiveCell.Value / 10)
ActiveCell.Offset(1, 0).Select
ElseIf (ActiveCell.Value > 9999 And ActiveCell.Value <= 99999) Then
ActiveCell.Value = (ActiveCell.Value / 100)
ActiveCell.Offset(1, 0).Select
ElseIf ActiveCell.Value > 99999 Then
ActiveCell.Value = (ActiveCell.Value / 1000)
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Wend

Range("E1", ActiveCell).Select

Selection.NumberFormat = "0.0"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
[/VBA]

My problem now is that this little trick will not work for column K in my excel sheet, does anyone have any ideas on how to replace all the "." with "," in the entire document ?? Please help, i am totally lost...

I have attached the xls document i am working on.

[Bob Phillips]

Difficult to test due to my settings, but does this work for you

[vba]

Sub ProcessData(StartCell As Range)
Dim cell As Range
'** Format and replace "." with "" in Weight field.
Range(StartCell, StartCell.End(xlDown)).Replace _
What:=".", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
'** Division of weights with 10,100 or 1000 & convertion from text to number format.
'** Selection centered.
For Each cell In Range(StartCell, StartCell.End(xlDown))
If cell.Value <= 999 Then
cell.Value = (cell.Value & ",0")
cell.Value = cell.Value * 1
cell.Offset(1, 0).Select
ElseIf (cell.Value > 999 And cell.Value <= 9999) Then
cell.Value = (cell.Value / 10)
cell.Offset(1, 0).Select
ElseIf (cell.Value > 9999 And cell.Value <= 99999) Then
cell.Value = (cell.Value / 100)
cell.Offset(1, 0).Select
ElseIf cell.Value > 99999 Then
cell.Value = (cell.Value / 1000)
cell.Offset(1, 0).Select
Else
cell.Offset(1, 0).Select
End If
Next cell

With StartCell
.NumberFormat = "0.0"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
[/vba]

use like

[vba]

ProcessData Range("E1")
[/vba]

and so on

[WebGuy]

It works perfectly for every column except Col K . The "," is supposed to end up where the "." is , this does not happen in col K due to the fact that the number of digits after "." varies. Run the script and look at K 12.
K12 before the script is 8.10552 where as K13 is 28,9834 so the division with 10, 100 & 1000 does not work properly for Column K(due to the random number of digits after the "."). That is why i am trying to find a sollution that will replace the "." with "," in the entire documents without trying to "Cheat" XL.