PDA

View Full Version : Solved: formatt numbers macro



Pete
12-02-2009, 08:09 AM
Not sure on how to finish this macro.....

Need the macro to do the following in column G with header "Value in Obj. Crcy".

1. replace all number with . to blanks

2. replace all numbers with , to .

then format number with comma seperator...i.e. "#,##0.00" until last row in columns G....


Sub Edit_4()


Application.ScreenUpdating = False

For i = 1 To lastrowM
'1. Column M (find and replace number with . replace with blank i.e. 13.43 to 1343)
.Cells(i, "M") = --Replace(CStr(.Cells(i, "M")), ".", "")
'2. Column M (find and replace , with .)
.Cells(i, "M") = Replace(CStr(.Cells(i, "M")), ";", ".")
Next i

'3. Format number in column G i.e. selecting formating on tool bar then cell
' then number the checking the Use 1000 separator box
.Columns("M:M").NumberFormat = "#,##0.00"

Application.ScreenUpdating = True

End Sub

Bob Phillips
12-02-2009, 08:31 AM
Sub Edit_4()
Dim i As Long
Dim lastrowM As Long

Application.ScreenUpdating = False

With ActiveSheet

lastrowM = .Cells(.Rows.Count, "M").End(xlUp).Row
For i = 1 To lastrowM
.Cells(i, "M") = Replace(CStr(.Cells(i, "M")), ".", "")
.Cells(i, "M") = Replace(CStr(.Cells(i, "M")), ",", ".")
Next i

.Columns("M:M").NumberFormat = "#,##0.00"
End With

Application.ScreenUpdating = True

End Sub

Pete
12-02-2009, 08:45 AM
thanks xld works prefectly