Code to count number of cells

[hsandeep]

In my Workbook there are numerous Worksheets out of which approximately 30 ws are using ‘Worksheet code’. In ‘ThisWorkbook’ also I am using a macro.
In Module, I am using a ‘vba code’ for Worksheet (tab named “ZZZ”) using 'User Defined Function' to get the output in some cells wherever UDF is there.
1 line in between the code being used is

y = Application.WorksheetFunction.CountIf(Range("D10:M10"), "=1")

I want to add another line in this macro which would check & perform some actions if…..
If y>(count of) number of cells from D10:M10 Then
Macro
End If

I could have used the code line

If y>10 Then
Macro
End If

But I want macro to do this counting in Worksheet (tab named “ZZZ”)
How to accomplish?
Thanks in advance

 

[Z51]

Instead of just

Range("D10:M10")

Specify the worksheet, e.g.

y = Application.WorksheetFunction.CountIf(Sheets("ZZZ").Range("D10:M10"), "=1")

 

[hsandeep]

Instead of just

Range("D10:M10")

Specify the worksheet, e.g.

y = Application.WorksheetFunction.CountIf(Sheets("ZZZ").Range("D10:M10"), "=1")

Thanks pjoaquin for trying to render help. Modification in the code's line well accepted & appreciated. But what about a code line (requested) to count the number of cells D10:M10? I know that the answer is 10 but I need a code line to do it

 

[Z51]

Range("D10:M10").Cells.Count ?

 

[hsandeep]

Range("D10:M10").Cells.Count ?

pjoaquin,
Should I prefix it with Application.WorksheetFunction & Sheets("ZZZ".....before Range....
If yes, please give me the complete code line

 

[Z51]

It's usually good to preface Range statements with the worksheet they belong to, especially if you have multiple worksheets in the workbook and you're using code to manipulate them. It just makes it clearer what cells you're actually working on.

You don't need the Application.WorksheetFunction in this case, because it's not using the worksheet function "count", it's using a built-in property of the Range object. Range().Cells.Count simply counts the number of cells in that range, whether they have a value or not. Using Application.WorksheetFunction.Count(Range("D5:M5")) would only count the number of cells that have a numeric value in them.

 

[hsandeep]

It's usually good to preface Range statements with the worksheet they belong to, especially if you have multiple worksheets in the workbook and you're using code to manipulate them. It just makes it clearer what cells you're actually working on.

You don't need the Application.WorksheetFunction in this case, because it's not using the worksheet function "count", it's using a built-in property of the Range object. Range().Cells.Count simply counts the number of cells in that range, whether they have a value or not. Using Application.WorksheetFunction.Count(Range("D5:M5")) would only count the number of cells that have a numeric value in them.

What about 'prefixing Sheets("ZZZ".....before Range....
If useful, please provide me the code line, pjoaquin

 

[Z51]

See code from post #2. That's literally all there is to it.

 

[hsandeep]

See code from post #2. That's literally all there is to it.

So will this code line work...
x=Sheets("ZZZ").Range("D10:M10").Cells.Count

 

[Z51]

Yes