I modified the formula

My suggestion was to modify the format, not the formula. Send me a workbook
that demonstrates this phenomenon and I'll try to fix it. I don't need the
entire workbook, just the few cells that recreate the problem.

--
Jim
"URW" wrote in message
...
| Thanks for the reply Jim, but it does not work. I modified the formula in
one
| of the cells as you suggested and the zero still shows.
|
| BTW, the summation value was exactly zero anyway because I am summing only
1
| column which is 0 in this particular instance. Even if the column has no
| values in any of its cells, the sum at the bottom still shows a 0, even if
I
| round as you suggest.
|
| Any other ideas?
|
|
| "Jim Rech" wrote:
|
| I tried using a format string, setting the numberFormat propery for
the
| cells in question to "#,##0_);(#,##0)" but that only works for cells
| without a formula.
|
| Number formatting works with formulas as well as numbers. Just as long
as
| the result is a value.
|
| This number format suppresses the display of 0 (note the final
semicolon):
|
| #,##0.00_);(#,##0.00);
|
| But the cell has to equal _exactly_ zero, not e.g., .0000001. To insure
a
| near-zero is zero use ROUND:
|
| =ROUND(SUM(A1:A10),0)
|
| --
| Jim
| "URW" wrote in message
| ...
| | Hi all,
| |
| | I am new to VBA and Excel programming and have come across the old
problem
| | of hiding 0's in cells with formulas. I know I can unset the zero
values
| | option, but I need to do this to 2 sheets in some 50 workbooks, so I
want
| to
| | do this in code. The workbooks are updated every so often using an
Access
| | database and VBA and the 2 sheets I am dealing with are added to each
| during
| | the update process. I tried using
| |
| | Application.ActiveWindow.DisplayZeros = False
| |
| | but that gives me a Macro security warning when the file is opened and
I
| | don't want that, nor do I want to reduce the security to low to get
around
| | the warning.
| |
| | I tried using a format string, setting the numberFormat propery for
the
| | cells in question to "#,##0_);(#,##0)" but that only works for cells
| without
| | a formula.
| |
| | Now I am stuck and don't know what else to try. I have found all kinds
of
| | solutions on the web, but they all involve setting something in Excel
| which I
| | don't want to do, because I would have to do it in 50 or so workbooks.
| |
| | Does anyone here know how to hide zeros in a cell with a formula in
VBA
| code?
| |
| | The formula is just a summation to total the values in the column, if
that
| | makes a difference.
| |
| | If you have some ideas you are willing to share or know for sure this
| can't
| | be done, please reply to my post. I would be very appreciative for the
| help.
| |
| | Thanks
| |
| | URW
|
|
|