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

PokerJoe

Board Regular
Joined
Aug 27, 2005
Messages
63
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1Day1st2nd3rd4th5thABCDEFGHIJKLMNOPQRSTUVWXYZ
21ABCDE
32FGHIJ
43KLMNO
54PQRST
65UVWXY
76ZGHIJ
87CHMRW
98PQRST
109QVGHK
1110FECKW
1211CTRHA
1312XGYKF
1413UVWXY
1514ZGHIJ
1615CHMRW
1716PQRST
1817QVGHY
1918CHMRW
2019PQRST
2120RVGHQ
2221FECKW
2322CTRHA
2423XGYKF
2524UVWXY
2625PQRST
2726QVGHT
2827CHMRW
2928PQRST
3029ABCDE
3130FGHIJ
3231KLMNO4210246101444615116101369379562
3332PQRST319136101444726226101369379562
3433UVWXY319135913337262271114710379562
3534ZGHIJ3191359133361511711147104810672
3635CHMRW3191351014446151161013694810673
3736VMRCT31101351015446161161014693710563
3837ERSXG31111359143361711610156103810562
3938TGHUV31101451013336161161015710389662
4039GCKPQ3110145111433616115914610499662
4140VRHQV3111145111333616116914610489662
4241GYKCH311013411143351611610156104108662
4342HWXPQ21101341214336161161014694108672
4443QRSAB21101331115335161171114694109662
4544VGHFG3210133111533516117121579398552
4645HMRKL32101341215225161171215793108551
4746TRSPQ3291341215226261171215793107551
4847VPHUO3291341215226261171215793107551
4948ABCDE3291341115226261281115794107541
5049FGHIJ4392441114226251281114794106541
5150KLMNO4392451215336251271013684106541
5251PQRST43924511143373623791268496541
5352UVWXY438234111433636238101379495541
5453ZGHIJ3372341113336362381012785106651
5554CHMRW3372331114445362381012785106542
5655VMRCT338233111544537238101378496432
5756ERSXG339233111544538237913684106432
5857TGHUV33924311144453823781477496532
5958GCKPQ338243121444537237813785105532
6059VRHQV339243131444637237812675105532
6160GYKCH228133131544637237913675125532
6261HWXPQ
6362QRSAB
6463VGHFG
6564HMRKL
6665TRSPQ
Sample43
Cell Formulas
RangeFormula
H32:H61H32=COUNTIF(B2:F31,$H$1)
I32:I61I32=COUNTIF(B2:F31,$I$1)
J32:J61J32=COUNTIF(B2:F31,$J$1)
K32:K61K32=COUNTIF(B2:F31,$K$1)
L32:L61L32=COUNTIF(B2:F31,$L$1)
M32:M61M32=COUNTIF(B2:F31,$M$1)
N32:N61N32=COUNTIF(B2:F31,$N$1)
O32:O61O32=COUNTIF(B2:F31,$O$1)
P32:P61P32=COUNTIF(B2:F31,$P$1)
Q32:Q61Q32=COUNTIF(B2:F31,$Q$1)
R32:R61R32=COUNTIF(B2:F31,$R$1)
S32:S61S32=COUNTIF(B2:F31,$S$1)
T32:T61T32=COUNTIF(B2:F31,$T$1)
U32:U61U32=COUNTIF(B2:F31,$U$1)
V32:V61V32=COUNTIF(B2:F31,$V$1)
W32:W61W32=COUNTIF(B2:F31,$W$1)
X32:X61X32=COUNTIF(B2:F31,$X$1)
Y32:Y61Y32=COUNTIF(B2:F31,$Y$1)
Z32:Z61Z32=COUNTIF(B2:F31,$Z$1)
AA32:AA61AA32=COUNTIF(B2:F31,$AA$1)
AB32:AB61AB32=COUNTIF(B2:F31,$AB$1)
AC32:AC61AC32=COUNTIF(B2:F31,$AC$1)
AD32:AD61AD32=COUNTIF(B2:F31,$AD$1)
AE32:AE61AE32=COUNTIF(B2:F31,$AE$1)
AF32:AF61AF32=COUNTIF(B2:F31,$AF$1)
AG32:AG61AG32=COUNTIF(B2:F31,$AG$1)
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

cortexnotion

Board Regular
Joined
Jan 22, 2020
Messages
135
Office Version
  1. 2013
Platform
  1. Windows
You can declare the search range as it will save you stating it for every formula. Your VBA equivalent will like this:

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,785
Messages
5,574,262
Members
412,581
Latest member
Rami100
Top