Count non-zero cells
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 |
Count non-zero cells
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 |
Count non-zero cells
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" wrote in message ... 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 |
Count non-zero cells
Mike, Roger
You're absolutely right. A typo. Thanks. Otto "Otto Moehrbach" wrote in message ... 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 |
All times are GMT +1. The time now is 10:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com