How to use CountIf in VBA to return a value, not embed a CountIf formula in the cell

[PokerJoe]

I need help figuring out how to write CountIf within a subroutine that returns a value rather than inserts a formula into a cell.

I'm pretty sure I have to use WorksheetFunction.CountIf but can't wrap my mind on how to use it and make it flow (For Each cell in Range).

Also, not sure if I need to Dim ranges for Arg1, Arg2 if I want to use relative reference.

So, I think it should go something like this but obviously I'm not sure.

Sub CountIf_Last_30_Days()

Dim LastRow As Long
'Dim c As Range, d As Range?
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

'WorksheetFunction.CountIf(Range(???

Application.ScreenUpdating = True

End Sub

Not sure if I've explained this well enough or if you'll be able to figure out what I'm trying to accomplish using the example provided, so, I'll answer specific questions.

Thanks in advance to anyone who is willing to take a crack at it

Group Assignment.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	Day	1st	2nd	3rd	4th	5th		A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
2	1	A	B	C	D	E
3	2	F	G	H	I	J
4	3	K	L	M	N	O
5	4	P	Q	R	S	T
6	5	U	V	W	X	Y
7	6	Z	G	H	I	J
8	7	C	H	M	R	W
9	8	P	Q	R	S	T
10	9	Q	V	G	H	K
11	10	F	E	C	K	W
12	11	C	T	R	H	A
13	12	X	G	Y	K	F
14	13	U	V	W	X	Y
15	14	Z	G	H	I	J
16	15	C	H	M	R	W
17	16	P	Q	R	S	T
18	17	Q	V	G	H	Y
19	18	C	H	M	R	W
20	19	P	Q	R	S	T
21	20	R	V	G	H	Q
22	21	F	E	C	K	W
23	22	C	T	R	H	A
24	23	X	G	Y	K	F
25	24	U	V	W	X	Y
26	25	P	Q	R	S	T
27	26	Q	V	G	H	T
28	27	C	H	M	R	W
29	28	P	Q	R	S	T
30	29	A	B	C	D	E
31	30	F	G	H	I	J
32	31	K	L	M	N	O		4	2	10	2	4	6	10	14	4	4	6	1	5	1	1	6	10	13	6	9	3	7	9	5	6	2
33	32	P	Q	R	S	T		3	1	9	1	3	6	10	14	4	4	7	2	6	2	2	6	10	13	6	9	3	7	9	5	6	2
34	33	U	V	W	X	Y		3	1	9	1	3	5	9	13	3	3	7	2	6	2	2	7	11	14	7	10	3	7	9	5	6	2
35	34	Z	G	H	I	J		3	1	9	1	3	5	9	13	3	3	6	1	5	1	1	7	11	14	7	10	4	8	10	6	7	2
36	35	C	H	M	R	W		3	1	9	1	3	5	10	14	4	4	6	1	5	1	1	6	10	13	6	9	4	8	10	6	7	3
37	36	V	M	R	C	T		3	1	10	1	3	5	10	15	4	4	6	1	6	1	1	6	10	14	6	9	3	7	10	5	6	3
38	37	E	R	S	X	G		3	1	11	1	3	5	9	14	3	3	6	1	7	1	1	6	10	15	6	10	3	8	10	5	6	2
39	38	T	G	H	U	V		3	1	10	1	4	5	10	13	3	3	6	1	6	1	1	6	10	15	7	10	3	8	9	6	6	2
40	39	G	C	K	P	Q		3	1	10	1	4	5	11	14	3	3	6	1	6	1	1	5	9	14	6	10	4	9	9	6	6	2
41	40	V	R	H	Q	V		3	1	11	1	4	5	11	13	3	3	6	1	6	1	1	6	9	14	6	10	4	8	9	6	6	2
42	41	G	Y	K	C	H		3	1	10	1	3	4	11	14	3	3	5	1	6	1	1	6	10	15	6	10	4	10	8	6	6	2
43	42	H	W	X	P	Q		2	1	10	1	3	4	12	14	3	3	6	1	6	1	1	6	10	14	6	9	4	10	8	6	7	2
44	43	Q	R	S	A	B		2	1	10	1	3	3	11	15	3	3	5	1	6	1	1	7	11	14	6	9	4	10	9	6	6	2
45	44	V	G	H	F	G		3	2	10	1	3	3	11	15	3	3	5	1	6	1	1	7	12	15	7	9	3	9	8	5	5	2
46	45	H	M	R	K	L		3	2	10	1	3	4	12	15	2	2	5	1	6	1	1	7	12	15	7	9	3	10	8	5	5	1
47	46	T	R	S	P	Q		3	2	9	1	3	4	12	15	2	2	6	2	6	1	1	7	12	15	7	9	3	10	7	5	5	1
48	47	V	P	H	U	O		3	2	9	1	3	4	12	15	2	2	6	2	6	1	1	7	12	15	7	9	3	10	7	5	5	1
49	48	A	B	C	D	E		3	2	9	1	3	4	11	15	2	2	6	2	6	1	2	8	11	15	7	9	4	10	7	5	4	1
50	49	F	G	H	I	J		4	3	9	2	4	4	11	14	2	2	6	2	5	1	2	8	11	14	7	9	4	10	6	5	4	1
51	50	K	L	M	N	O		4	3	9	2	4	5	12	15	3	3	6	2	5	1	2	7	10	13	6	8	4	10	6	5	4	1
52	51	P	Q	R	S	T		4	3	9	2	4	5	11	14	3	3	7	3	6	2	3	7	9	12	6	8	4	9	6	5	4	1
53	52	U	V	W	X	Y		4	3	8	2	3	4	11	14	3	3	6	3	6	2	3	8	10	13	7	9	4	9	5	5	4	1
54	53	Z	G	H	I	J		3	3	7	2	3	4	11	13	3	3	6	3	6	2	3	8	10	12	7	8	5	10	6	6	5	1
55	54	C	H	M	R	W		3	3	7	2	3	3	11	14	4	4	5	3	6	2	3	8	10	12	7	8	5	10	6	5	4	2
56	55	V	M	R	C	T		3	3	8	2	3	3	11	15	4	4	5	3	7	2	3	8	10	13	7	8	4	9	6	4	3	2
57	56	E	R	S	X	G		3	3	9	2	3	3	11	15	4	4	5	3	8	2	3	7	9	13	6	8	4	10	6	4	3	2
58	57	T	G	H	U	V		3	3	9	2	4	3	11	14	4	4	5	3	8	2	3	7	8	14	7	7	4	9	6	5	3	2
59	58	G	C	K	P	Q		3	3	8	2	4	3	12	14	4	4	5	3	7	2	3	7	8	13	7	8	5	10	5	5	3	2
60	59	V	R	H	Q	V		3	3	9	2	4	3	13	14	4	4	6	3	7	2	3	7	8	12	6	7	5	10	5	5	3	2
61	60	G	Y	K	C	H		2	2	8	1	3	3	13	15	4	4	6	3	7	2	3	7	9	13	6	7	5	12	5	5	3	2
62	61	H	W	X	P	Q
63	62	Q	R	S	A	B
64	63	V	G	H	F	G
65	64	H	M	R	K	L
66	65	T	R	S	P	Q
Sample43

Cell Formulas
Range	Formula
H32:H61	H32	=COUNTIF(B2:F31,$H$1)
I32:I61	I32	=COUNTIF(B2:F31,$I$1)
J32:J61	J32	=COUNTIF(B2:F31,$J$1)
K32:K61	K32	=COUNTIF(B2:F31,$K$1)
L32:L61	L32	=COUNTIF(B2:F31,$L$1)
M32:M61	M32	=COUNTIF(B2:F31,$M$1)
N32:N61	N32	=COUNTIF(B2:F31,$N$1)
O32:O61	O32	=COUNTIF(B2:F31,$O$1)
P32:P61	P32	=COUNTIF(B2:F31,$P$1)
Q32:Q61	Q32	=COUNTIF(B2:F31,$Q$1)
R32:R61	R32	=COUNTIF(B2:F31,$R$1)
S32:S61	S32	=COUNTIF(B2:F31,$S$1)
T32:T61	T32	=COUNTIF(B2:F31,$T$1)
U32:U61	U32	=COUNTIF(B2:F31,$U$1)
V32:V61	V32	=COUNTIF(B2:F31,$V$1)
W32:W61	W32	=COUNTIF(B2:F31,$W$1)
X32:X61	X32	=COUNTIF(B2:F31,$X$1)
Y32:Y61	Y32	=COUNTIF(B2:F31,$Y$1)
Z32:Z61	Z32	=COUNTIF(B2:F31,$Z$1)
AA32:AA61	AA32	=COUNTIF(B2:F31,$AA$1)
AB32:AB61	AB32	=COUNTIF(B2:F31,$AB$1)
AC32:AC61	AC32	=COUNTIF(B2:F31,$AC$1)
AD32:AD61	AD32	=COUNTIF(B2:F31,$AD$1)
AE32:AE61	AE32	=COUNTIF(B2:F31,$AE$1)
AF32:AF61	AF32	=COUNTIF(B2:F31,$AF$1)
AG32:AG61	AG32	=COUNTIF(B2:F31,$AG$1)

 

[cortexnotion]

You can declare the search range as it will save you stating it for every formula. Your VBA equivalent will like this:

WorksheetFunction.CountIf(.Range("B2:F31"),.Range("H1"))

You could put the Column numbers into an array and loop the through them to populate your calculations. Another option would be to use VBA to place the formulas into the cells and then change the results to values.