I have code that takes values from the spreadsheet and stores them into an array (Array named JanDates) those values are the days of every month in a year taken from a spreadsheet. I then have another array to store values from a CountIF function that counts every instance of (JanDates) in a column.
The issue is that I get a Subscript out of Range error when the amount of values in the column exceed the array size, but the array size is directly linked to the amount of dates in the month and cannot be changed.
Some context for the arrays in the code below. Month dates are stored in rows every 5th row, that's why I step 5 and count the columns in that row to get the amount of days in that month.
JanDates is the actual value of the dates in those rows which is used to run the CountIF.
ComLength is the amount of entries in the column (the entries are in date format).
I hope it makes sense, any support would be appreciated.
Dim ComLength As Long
Dim x As Long
Dim LetterSht As Variant
Dim FrstLtr As Variant
Dim JanDates() As Variant
Dim MnthCount As Long
Dim MnthDayCount As Long
Dim MnthDates As Long
Dim d As Long
LetterSht = (Array("First Letter", "Second Letter", "Third Letter"))
For x = LBound(LetterSht) To UBound(LetterSht)
With Worksheets(LetterSht(x))
ComLength = .Range("J" & Rows.Count).End(xlUp).Row
If ActiveSheet.Name = "First Letter" Then
ReDim FrstLtr(2 To ComLength)
For MnthCount = 2 To 57 Step 5
MnthDayCount = WorksheetFunction.CountA(Worksheets("Log").Range("F" & MnthCount & ":AJ" & MnthCount))
JanDates = Worksheets("Log").Range("F" & MnthCount & ":AJ" & MnthCount).Value2
ReDim FrstLtr(1 To MnthDayCount)
For d = 1 To MnthDayCount
FrstLtr(d) = WorksheetFunction.CountIfs(.Range("K2:K" & ComLength), "=" & JanDates(1, d))
If d = MnthDayCount Then
Worksheets("Log").Range("F" & MnthCount + 4 & ":AJ" & MnthCount + 4) = Application.WorksheetFunction.Transpose(FrstLtr)
End If
Next
Next
End If
End With
Next x End Sub