Excel XP & Win XP
I have a sheet with zero values suppressed (Tools - Options - View tab -
Uncheck Zero values).
I have a range, say F13:F16.
Each cell has a formula that produces a number.
The result in one cell is zero so that cell appears blank.
I want to count the number of non-zero cells in the range.
The worksheet formula:
CountIf(F13:F16,"<>0")
produces the correct answer, 3.
I need to do this in VBA so I use:
Range("H7").Value = Application.CountIf(Range("F13:F17"), "<>0")
This produces a 4.
What is the correct syntax to produce a 3?
Thanks for your time. Otto

You have different ranges in your formula (F13:F16) & (F13:F17)

using the same range in both I get the same answer.

Mike

"Otto Moehrbach" wrote:

> Excel XP & Win XP
> I have a sheet with zero values suppressed (Tools - Options - View tab -
> Uncheck Zero values).
> I have a range, say F13:F16.
> Each cell has a formula that produces a number.
> The result in one cell is zero so that cell appears blank.
> I want to count the number of non-zero cells in the range.
> The worksheet formula:
> CountIf(F13:F16,"<>0")
> produces the correct answer, 3.
> I need to do this in VBA so I use:
> Range("H7").Value = Application.CountIf(Range("F13:F17"), "<>0")
> This produces a 4.
> What is the correct syntax to produce a 3?
> Thanks for your time. Otto
>
>
>
>

Hi Otto

Unless it was a typo when posting, you are looking at 2 different size
ranges
F13:F16 with formula
F13:F17 in code
This may well account for the different result.
--
Regards

Roger Govier


"Otto Moehrbach" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Excel XP & Win XP
> I have a sheet with zero values suppressed (Tools - Options - View
> tab - Uncheck Zero values).
> I have a range, say F13:F16.
> Each cell has a formula that produces a number.
> The result in one cell is zero so that cell appears blank.
> I want to count the number of non-zero cells in the range.
> The worksheet formula:
> CountIf(F13:F16,"<>0")
> produces the correct answer, 3.
> I need to do this in VBA so I use:
> Range("H7").Value = Application.CountIf(Range("F13:F17"), "<>0")
> This produces a 4.
> What is the correct syntax to produce a 3?
> Thanks for your time. Otto
>
>
>

Mike, Roger
You're absolutely right. A typo. Thanks. Otto
"Otto Moehrbach" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Excel XP & Win XP
> I have a sheet with zero values suppressed (Tools - Options - View tab -
> Uncheck Zero values).
> I have a range, say F13:F16.
> Each cell has a formula that produces a number.
> The result in one cell is zero so that cell appears blank.
> I want to count the number of non-zero cells in the range.
> The worksheet formula:
> CountIf(F13:F16,"<>0")
> produces the correct answer, 3.
> I need to do this in VBA so I use:
> Range("H7").Value = Application.CountIf(Range("F13:F17"), "<>0")
> This produces a 4.
> What is the correct syntax to produce a 3?
> Thanks for your time. Otto
>
>
>