Solved: Formatting Negative Numbers

[kathyb0527]

I have a macro that formats numbers to 3 significant figures using elseif statements. It works great on positive number, but I need to format negative numbers also (-1 should be -1.00). How do I modify my code to include negative numbers?

[vba]Dim rCell As Range
For Each rCell In Selection
If rCell.Value = 0 Then
rCell.NumberFormat = "0"
ElseIf rCell.Value < 0.001 Then
rCell.NumberFormat = "0.000000"
ElseIf rCell.Value < 0.01 Then
rCell.NumberFormat = "0.00000"
ElseIf rCell.Value < 0.1 Then
rCell.NumberFormat = "0.0000"
ElseIf rCell.Value < 1 Then
rCell.NumberFormat = "0.000"
ElseIf rCell.Value < 10 Then
rCell.NumberFormat = "0.00"
ElseIf rCell.Value < 100 Then
rCell.NumberFormat = "0.0"
ElseIf rCell.Value < 1000 Then
rCell.NumberFormat = "0"
End If
Next rCell[/vba]

Thanks for the help (again!)

[Bob Phillips]

Dim rCell As Range
For Each rCell In Selection
    If rCell.Value < -1000 Then
        rCell.NumberFormat = "(0.0)"
    ElseIf rCell.Value < -100 Then
        rCell.NumberFormat = "(0.00)"
    ElseIf rCell.Value < -10 Then
        rCell.NumberFormat = "(0)"
    ElseIf rCell.Value = 0 Then
        rCell.NumberFormat = "0"
    ElseIf rCell.Value < 0.001 Then
        rCell.NumberFormat = "0.000000"
    ElseIf rCell.Value < 0.01 Then
        rCell.NumberFormat = "0.00000"
    ElseIf rCell.Value < 0.1 Then
        rCell.NumberFormat = "0.0000"
    ElseIf rCell.Value < 1 Then
        rCell.NumberFormat = "0.000"
    ElseIf rCell.Value < 10 Then
        rCell.NumberFormat = "0.00"
    ElseIf rCell.Value < 100 Then
        rCell.NumberFormat = "0.0"
    ElseIf rCell.Value < 1000 Then
        rCell.NumberFormat = "0"
    End If
Next rCell

[kathyb0527]

Hi XLD,
Thanks for the quick reply. Unfortunately, the negative numbers get formatted to -1.000000 I think because of the line

[vba]ElseIf rCell.Value < 0.001 Then
rCell.NumberFormat = "0.000000"[/vba]

Would a select case work here? I'm not familiar with using this expression.

Kathyb0527

[Bob Phillips]

Select Case would work but no better IMO

[vba]

Dim rCell As Range
For Each rCell In Selection
If rCell.Value < -1000 Then
rCell.NumberFormat = "(0.0)"
ElseIf rCell.Value < -100 Then
rCell.NumberFormat = "(0.00)"
ElseIf rCell.Value < -10 Then
rCell.NumberFormat = "(0.0)"
ElseIf rCell.Value < 0 Then
rCell.NumberFormat = "(0)"
ElseIf rCell.Value = 0 Then
rCell.NumberFormat = "0"
ElseIf rCell.Value < 0.001 Then
rCell.NumberFormat = "0.000000"
ElseIf rCell.Value < 0.01 Then
rCell.NumberFormat = "0.00000"
ElseIf rCell.Value < 0.1 Then
rCell.NumberFormat = "0.0000"
ElseIf rCell.Value < 1 Then
rCell.NumberFormat = "0.000"
ElseIf rCell.Value < 10 Then
rCell.NumberFormat = "0.00"
ElseIf rCell.Value < 100 Then
rCell.NumberFormat = "0.0"
ElseIf rCell.Value < 1000 Then
rCell.NumberFormat = "0"
End If
Next rCell
[/vba]

[kathyb0527]

Still not quite right. Now the negative numbers 1-9 get formatted as -1 instead of -1.00.

I took another crack at it and I think I've got it

[vba]Dim rCell As Range
For Each rCell In Selection
If rCell.Value > -1 And rCell.Value < 0 Then
rCell.NumberFormat = "0.000"
ElseIf rCell.Value > -10 And rCell.Value < 0 Then
rCell.NumberFormat = "0.00"
ElseIf rCell.Value > -100 And rCell.Value < 0 Then
rCell.NumberFormat = "0.0"
ElseIf rCell.Value < -99.9 And rCell.Value < 0 Then
rCell.NumberFormat = "0"
ElseIf rCell.Value = 0 Then
rCell.NumberFormat = "0"
ElseIf rCell.Value < 0.001 And rCell.Value > 0 Then
rCell.NumberFormat = "0.000000"
ElseIf rCell.Value < 0.01 And rCell.Value > 0 Then
rCell.NumberFormat = "0.00000"
ElseIf rCell.Value < 0.1 And rCell.Value > 0 Then
rCell.NumberFormat = "0.0000"
ElseIf rCell.Value < 1 And rCell.Value > 0 Then
rCell.NumberFormat = "0.000"
ElseIf rCell.Value < 10 And rCell.Value > 0 Then
rCell.NumberFormat = "0.00"
ElseIf rCell.Value < 100 And rCell.Value > 0 Then
rCell.NumberFormat = "0.0"
ElseIf rCell.Value > 99.9 And rCell.Value > 0 Then
rCell.NumberFormat = "0"
End If
Next rCell
[/vba]