VBA CountIfs - Transpose returning 0 (Boolean) values

[Lbdch]

Hello, I have some code that I'm struggling with that works fine as simple formulas but doesn't want to work at all as code. The code is simply a countifs from a range counting if values match 2 different given conditions. But for some reason the countifs doesnt actually return any values. Both "EntryDates" and "EntrySheet" are the ranges for the countifs from the below.

Compliance Amendments Tracked_Amended.xlsm
	A	B	C
1	Time changed	Sheet Name	Log ID
2	-----------	-----------	-----------
3	01/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I688:K688
4	01/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I689:K689
5	02/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I690:K690
6	02/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I691:K691
7	03/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I692:K692
8	04/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I698:K698
9	04/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I699:K699
10	04/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I706:K706
11	05/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I718:K718
12	05/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I693:K693
13	06/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I694:K694
14	07/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I695:K695
15	08/07/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I696:K696
16	09/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I697:K697
17	09/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I700:K700
18	09/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I701:K701
19	10/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I702:K702
20	11/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I703:K703
21	11/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I704:K704
22	11/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I705:K705
23	06/06/2022	C285 Repayment Overpayment	C285 Repayment Overpayment!I707:K707
Entry Log

The "Ident" is the name on the left hand side of the below and "MonthDays(x)" are conditions for the countifs that are taken from the below. With each iteration of (x) MonthDays is each day in the month that is being iterated through.

Compliance Amendments Tracked_Amended.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
56	June	1st	2nd	3rd	4th	5th	6th	7th	8th	9th	10th	11th	12th	13th	14th	15th	16th	17th	18th	19th	20th	21st	22nd	23rd	24th	25th	26th	27th	28th	29th	30th
57	C285 Repayment Overpayment	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
58	C2001 Voluntary Underpayment	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
59	Wrong Importer Amendment	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
60	PVA Amendment No Further Action	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
61
62	C285 Completed	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
63	C2001 Completed	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
64	Wrong Importer Completed	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
65	PVA Amendment Completed	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Tracker

Dim CurrMonth As String
     Dim PosMatch As Variant
     Dim MonthDayCount As Variant
     Dim MonthDays As Variant
     Dim C285 As Variant
     Dim EntryDates As Range
     Dim EntrySheet As Range
     Dim LastRow As Integer
     Dim Ident As Variant
     Dim p As Integer
     Dim x As Integer
     
     CurrMonth = Format$(Date, "MMMM")
     
     With Worksheets("Entry Log")
     LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
     
     Set EntryDates = .Range("A3" & ":A" & LastRow)
     Set EntrySheet = .Range("B3" & ":B" & LastRow)
     
     End With
     
     With Worksheets("Tracker")
     PosMatch = Application.Match(CurrMonth, .Range("A:A"), 0)

     MonthDayCount = .Cells(PosMatch, "A").End(xlToRight).Column - 1
     
     MonthDays = Application.Index(.Range(.Cells(PosMatch, 2), .Cells(PosMatch, MonthDayCount + 1)).Value, 1, 0)
     
     ReDim C285(1 To MonthDayCount)
     
     For p = 1 To 4
     Ident = .Range("A" & PosMatch).Offset(p, 0)
     
     For x = 1 To MonthDayCount
     
     C285(x) = Application.CountIfs(EntrySheet, Ident, EntryDates, MonthDays(x))
     
     If x = MonthDayCount Then
     .Range(.Cells(PosMatch + p, 2), .Cells(PosMatch + p, MonthDayCount + 1)).Value = Application.WorksheetFunction.Transpose(C285)
     End If
     
    Next x
    Next p
    End With
End Sub

 

[Fluff]

With that data I get values being returned, although you need to get rid of the transpose

     .Range(.Cells(PosMatch + p, 2), .Cells(PosMatch + p, MonthDayCount + 1)).Value = C285

 

[Lbdch]

@Fluff I changed the code to as above, it is returning only 2 values on the 6th but it should definitely return more values with the data set I have provided above. The first line alone should return multiple dates.

 

[Fluff]

As the data in the op only covers "C285 Repayment Overpayment" I cannot check further, but this is what I get.

Fluff.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
56	June	44713	44714	44715	44716	44717	44718	44719	44720	44721	44722	44723	44724	44725	44726	44727	44728	44729	44730	44731	44732	44733	44734	44735	44736	44737	44738	44739	44740	44741	44742
57	C285 Repayment Overpayment	2	2	1	3	2	2	1	0	3	1	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
58	C2001 Voluntary Underpayment	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Tracker

 

[Lbdch]

@Fluff Ahh, the formatting that I had set to change the number into date was causing the issue, I'm not sure as to why though.

 

[Fluff]

Your code is not returning dates.