Sub Test() Dim r, x
Application.ScreenUpdating = False
x = Application.DecimalSeparator
With ActiveSheet
Set r = Intersect(.Range("D:G"), .UsedRange)
With r
.Replace What:=",", Replacement:=x, LookAt:=xlPart
.Replace What:=".", Replacement:=x, LookAt:=xlPart
numF = "0" & x & "00;-0" & x & "00;0" & x & "00"
.Columns("B:D").NumberFormat = numF
For Each cel In r.Offset(1).Columns(1).Cells
If InStr(1, cel, "M") Then
cel.Replace What:="M", Replacement:="", LookAt:=xlPart
cel.Value = cel.Value * 10 ^ 6
cel.NumberFormat = "0" & x & "00,,""M"""
End If
If InStr(1, cel, "B") Then
cel.Replace What:="B", Replacement:="", LookAt:=xlPart
cel.Value = cel.Value * 10 ^ 9
cel.NumberFormat = "0" & x & "00,,,""B"""
End If
Next
End With
End With
Application.ScreenUpdating = True
End Sub