WebGuy
06-12-2007, 08:26 AM
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 :
'** 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
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. :banghead:
'** 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
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. :banghead: