PDA

View Full Version : Solved: Formatting Negative Numbers



kathyb0527
12-09-2008, 02:39 PM
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?

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

Thanks for the help (again!)

Bob Phillips
12-09-2008, 02:57 PM
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
12-09-2008, 03:40 PM
Hi XLD,
Thanks for the quick reply. Unfortunately, the negative numbers get formatted to -1.000000 I think because of the line

ElseIf rCell.Value < 0.001 Then
rCell.NumberFormat = "0.000000"

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

Kathyb0527

Bob Phillips
12-09-2008, 05:24 PM
Select Case would work but no better IMO



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

kathyb0527
12-10-2008, 12:36 PM
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

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