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.
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 | |||
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 |
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