Countif() giving different values ?

Skydyno

New Member
Joined
Aug 22, 2014
Messages
9
Hi All

Row 6:6 contains a mixture of cells containing text and formula that return numbers. I wish to count the either the number of numbers or the number of non empty cells.

The number of non blank cells, either with text(or a formula returning text is constant at 3) or a number (varies) is 7. Therefore the number of rows containing a number atm is 4


What I don't understand is why I'm getting diferent results...:confused:

In the worksheet cell formula =COUNTIF(6:6,">0") - this returns 4


When I try to use VBA to do the same thing I get a different answer...

Code:
Check_1 = Application.WorksheetFunction.CountIf(Sheets(Which_Sheet).Range("6:6"), ">0")

this returns 1

As does

Code:
Check_2 = Worksheets(Which_Sheet).Range("6:6").Cells.SpecialCells(xlCellTypeConstants).Count


Is it to do with Rows? When I've used these in columns before they have returned the correct answers..


Sorry if I'm just being stupid...:rolleyes:
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Can't see why:


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A
[/TD]
[TD]
B
[/TD]
[TD]
C
[/TD]
[TD]
D
[/TD]
[TD]
E
[/TD]
[TD]
F
[/TD]
[TD]
G
[/TD]
[TD]
H
[/TD]
[/TR]
[TR]
[TD]
1
[/TD]
[TD]
a​
[/TD]
[TD]
b​
[/TD]
[TD]
c​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[TD]
4​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
4​
[/TD]
[TD]
=COUNTIF(1:1,">0")​
[/TD]
[/TR]
[TR]
[TD]
5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
?application.WorksheetFunction.CountIf(Rows("1:1"),">0")​
[/TD]
[/TR]
[TR]
[TD]
8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD]
9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
?application.WorksheetFunction.CountIf(Rows(1),">0")​
[/TD]
[/TR]
[TR]
[TD]
10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD]
11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
?application.WorksheetFunction.CountIf(Range("A1").EntireRow,">0")​
[/TD]
[/TR]
[TR]
[TD]
12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD]
13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
?application.WorksheetFunction.CountIf(Range("A1").EntireRow.SpecialCells(xlCellTypeConstants),">0")​
[/TD]
[/TR]
[TR]
[TD]
14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD]
15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
?application.WorksheetFunction.CountIf(Range("1:1"),">0")​
[/TD]
[/TR]
[TR]
[TD]
16
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD]
17
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
?application.WorksheetFunction.CountIf(Range("1:1").SpecialCells(xlCellTypeConstants),">0")​
[/TD]
[/TR]
[TR]
[TD]
18
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD]
19
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
?application.WorksheetFunction.Count(Range("1:1"))​
[/TD]
[/TR]
[TR]
[TD]
20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD]
21
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
?application.WorksheetFunction.CountA(Range("1:1"))​
[/TD]
[/TR]
[TR]
[TD]
22
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
7​
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Are you sure Which_Sheet is the correct sheet name?
 
Upvote 0
Hi

Maybe you can prepare a small example?

Remark:

Your second statement is not equivalent to the first one, The CountIf() counts number vales bigger than zero, while the .SpecialCells that you posted will count any type of constants.
 
Upvote 0
Well, as it turns out.... The answer was.... Yes, I'm stupid

Thanks for the replies. @TMShucks thanks, helped and simplified some other challenges.

Are you sure Which_Sheet is the correct sheet name?

That, as it turns out, was the problem. As you replied I figured out that the SHEETS("Name of sheet").RANGE.... (in my case Which_Sheet)... takes the left most sheettab in the workbook as 1 (Default name of Sheet1).

err..ok

So when you change it's name, the reference still remains 1. My workbook references different sheets based on a vlookup to return a number and goto that sheet.

Sheet1 and Sheet2 have been renamed. The rest of the sheets are renamed sequencially, 1,2,3... etc. So I've ended up with Sheets displaying one number and having a slightly different reference number.

My shee ttabs read... Menu, Input,1,2,3,4,...etc

In my code, Which_Sheet = 3 so SHEETS(WHICH_SHEET).RANGE... was compiled to SHEETS(3).RANGE... and SHEET(3) is the third one along which is infact labelled "1"

... so pulling the information from the wrong sheet. If you follow me...:eeek:

DOH.

With this is mind how do I reference sheet in VBA using thier visible tab name ?
 
Upvote 0

Forum statistics

Threads
1,223,151
Messages
6,170,381
Members
452,322
Latest member
CrimsonCoure

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top