VBA CountIfs - Transpose returning 0 (Boolean) values

Lbdch

New Member
Joined
Apr 25, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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
ABC
1Time changedSheet NameLog ID
2---------------------------------
301/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I688:K688
401/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I689:K689
502/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I690:K690
602/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I691:K691
703/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I692:K692
804/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I698:K698
904/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I699:K699
1004/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I706:K706
1105/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I718:K718
1205/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I693:K693
1306/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I694:K694
1407/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I695:K695
1508/07/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I696:K696
1609/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I697:K697
1709/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I700:K700
1809/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I701:K701
1910/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I702:K702
2011/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I703:K703
2111/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I704:K704
2211/06/2022C285 Repayment OverpaymentC285 Repayment Overpayment!I705:K705
2306/06/2022C285 Repayment OverpaymentC285 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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
56June1st2nd3rd4th5th6th7th8th9th10th11th12th13th14th15th16th17th18th19th20th21st22nd23rd24th25th26th27th28th29th30th
57C285 Repayment Overpayment000000000000000000000000000000
58C2001 Voluntary Underpayment000000000000000000000000000000
59Wrong Importer Amendment000000000000000000000000000000
60PVA Amendment No Further Action000000000000000000000000000000
61
62C285 Completed000000000000000000000000000000
63C2001 Completed000000000000000000000000000000
64Wrong Importer Completed000000000000000000000000000000
65PVA Amendment Completed000000000000000000000000000000
Tracker


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

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
With that data I get values being returned, although you need to get rid of the transpose
VBA Code:
     .Range(.Cells(PosMatch + p, 2), .Cells(PosMatch + p, MonthDayCount + 1)).Value = C285
 
Upvote 0
@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.
 
Upvote 0
As the data in the op only covers "C285 Repayment Overpayment" I cannot check further, but this is what I get.
Fluff.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
56June447134471444715447164471744718447194472044721447224472344724447254472644727447284472944730447314473244733447344473544736447374473844739447404474144742
57C285 Repayment Overpayment221322103130000000000000000000
58C2001 Voluntary Underpayment000000000000000000000000000000
Tracker
 
Upvote 0
@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.
 
Upvote 0
Your code is not returning dates. :unsure:
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top