VB Macro in Excel 2007

[asimmons]

I frequently need to format excel reports that show no values after a
decimal point if it is a two digit number and one value after the
decimal point if it is a single digit number. For example... 3.12,
20.78, -1.22 and -10.55 would be displayed 3.1, 21, (1), and (11). I
have created a macro that does this for numbers I type in however when I
try to expand it to select cells in my reports, I get an error code that
says 400. I'm not sure if I have the parameters set wrong or if it is
because the report cells are referencing formulas. I have attached my
code. Anybody have suggestions????

Sub MyFormat2()
Dim Cll As Range
For Each Cll In Selection.SpecialCells(xlCellTypeConstants,
xlNumbers).Cells
If Cll <= 10 And Cll >= -10 Then
Cll.NumberFormat = "#,##0.0_);(#,##0.0)"
Else
Cll.NumberFormat = "#,##0_);(#,##0)"
End If
Next Cll
End Sub

 

[Harlan Grove]

I frequently need to format excel reports that show no values after a
decimal point if it is a two digit number and one value after the
decimal point if it is a single digit number.  For example... 3.12,
20.78, -1.22 and -10.55 would be displayed 3.1, 21, (1), and (11).  I
have created a macro that does this for numbers I type in however when I
try to expand it to select cells in my reports, I get an error code that
says 400.  I'm not sure if I have the parameters set wrong or if it is
because the report cells are referencing formulas.  I have attached my
code.  Anybody have suggestions????

....

How big is your selection? If it's very big, it's possible you're
blowing through Excel's object model's limits on the number of areas
in a multiple area range. You may have to iterate through your
selection in smaller size chunks.

BTW, it'd be better to post this sort of question to the
microsoft.public.excel.programming newsgroup rather than this one. I'm
crossposting to microsoft.public.excel.programming and setting the
follow-up tag to that newsgroup.