vba code to count non-blank cells in a range

[tucsondonpepe]

I am trying to count the number of non-blank cells in 16 ranges on sheet "Signup" in column E. The ranges are stored in
LNCaptain(16). I store the results in TeamsInLeague(16).

The code is

I receive this error

I would appreciate youor help. Thank you.

 

[DanteAmor]

Hi and welcome to MrExcel.

The ranges are stored in LNCaptain(16)

How did you store the ranges in the LNCaptain variable?

Did you create a named range for each cell range of the LNCaptain?
If so, what is each named range called?

 

[tucsondonpepe]

Good morning and thank you for replying,

This is the subroutine.

==================================================================

==================================================================

The LNCaptain data is dfined in sheet Leagues.

===============================================================

===============================================================

This is output showing the value of LNCaptain(1).

=============================================================

=============================================================

This is the error message.

===============================================================================================================================

===============================================================================================================================

 

[Fluff]

Can you post the actual code, rather than an image. It saves us from having to re-type it. How to Post Your VBA Code

 

[kvsrinivasamurthy]

This code counts the blank cells in E2:E17 range

Dim Cnt as long
cnt=worksheetfunction.counta(Range("E2:E17")

 

[Joe4]

This code counts the blank cells in E2:E17 range

Dim Cnt as long
cnt=worksheetfunction.counta(Range("E2:E17")

That actually counts NON blank cells.
And you are missing a right parend on the end, i.e.

cnt=worksheetfunction.counta(Range("E2:E17"))

If you wanted to count blanks, you would use COUNTBLANK, not COUNTA.

 

[DanteAmor]

I am trying to count the number of non-blank cells in 16 ranges on sheet "Signup" in column E.

To paste examples from your sheet, you must use the XL2BB tool minisheets. It is easier for us to use data than an image. I show you an example:

varios 31ene2022.xlsm
	A	B	C	D	E
1	Leagues	PoT	Players	League Label	LNCaptain
2		4	16		E5:E8
3		2	8		E13:E16
4		1	4		E21:E24
5		2	8		E29:E32
6		4	16		E37:E40
7		1	4		E45:E48
8		4	16		E53:E56
9		2	8		E61:E64
10		1	4		E69:E72
11		2	8		E77:E80
12		1	4		E85:E88
13		4	16		E93:E96
14		4	16		E101:E104
15		2	8		E109:E112
16		4	16		E117:E120
17		4	16		E125:E128
18			168
Leagues

NOTE: You must put the ranges without quotes.

____
Try the following code:

Sub Count_NON_Blank()
  Dim n As Long
  Dim TeamsInLeague As Variant
  Dim x As Variant
  ReDim TeamsInLeague(1 To 16)
  For n = 1 To 16
    x = Sheets("Leagues").Range("E" & n + 1).Value
    TeamsInLeague(n) = WorksheetFunction.CountA(Sheets("Signup").Range(x))
    Debug.Print TeamsInLeague(n)
  Next
End Sub

You also need to comment on what the final goal of doing the count is, because storing the counts in a variable I don't think is the final goal.