Countif() giving different values ?

[Skydyno]

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...

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...

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

this returns 1

As does

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...

 

[TMShucks]

Can't see why:

	A​	B​	C​	D​	E​	F​	G​	H​
1​	a​	b​	c​	1​	2​	3​	4​
2​
3​
4​							4​	=COUNTIF(1:1,">0")​
5​
6​
7​								?application.WorksheetFunction.CountIf(Rows("1:1"),">0")​
8​								4​
9​								?application.WorksheetFunction.CountIf(Rows(1),">0")​
10​								4​
11​								?application.WorksheetFunction.CountIf(Range("A1").EntireRow,">0")​
12​								4​
13​								?application.WorksheetFunction.CountIf(Range("A1").EntireRow.SpecialCells(xlCellTypeConstants),">0")​
14​								4​
15​								?application.WorksheetFunction.CountIf(Range("1:1"),">0")​
16​								4​
17​								?application.WorksheetFunction.CountIf(Range("1:1").SpecialCells(xlCellTypeConstants),">0")​
18​								4​
19​								?application.WorksheetFunction.Count(Range("1:1"))​
20​								4​
21​								?application.WorksheetFunction.CountA(Range("1:1"))​
22​								7​

<tbody>
</tbody>

 

[RoryA]

Are you sure Which_Sheet is the correct sheet name?

 

[pgc01]

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.

 

[Skydyno]

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...

DOH.

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

 

[Skydyno]

RESOLVED.

Thanks to everyone for thier time and help...

 

[TMShucks]

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

Sheets("1")

as opposed to

Sheets(1)

 

[Skydyno]

Sheets("1")

as opposed to

Sheets(1)

Again thanks....