Hi, I need help with a date code that gives 'Type Mismatch' error and I would also like to modify the date code to exclude the current and previous month and only include all months prior. See notes in the code attached.

Code

- Option Base 1
- Sub DateMonths()
- Dim x, y, Cols, ContAreas, WrkClss, i As Long, ii As Long, iii As Long
- Const Category As String = "Standard of Work"
- Const InspType As String = "Post Work Manual"
- Const DtCol As Long = 10 '// target date col
- ContAreas = Array("SECA11", "SECA12", "SECA13", "SECA14", "SECA15", "SECA16")
- Cols = Array(1, 4, 7, 10, 13, 16, 19, 22, 25)
- WrkClss = Array("UW", "PW", "VAC*", "MPWCAP", "MOD*", "LGC", "BES", "MPWA", "SMOK")
- x = Sheets("calculations").[a6].CurrentRegion
- ReDim y(1 To 6, 1 To 26)
- For i = 2 To UBound(x, 1)
- For ii = 1 To 6
- 'the below code gives TYPE MISMATCH ERROR
- If Year(x(i, DtCol)) = Year(Date) And Month(x(i, DtCol)) = Month(Date) And x(i, 3) = ContAreas(ii) And x(i, 7) = Category And x(i, 8) = InspType Then 'get Type Mismatch error
- 'If Year(x(i, DtCol)) = Year(Date) And Month(x(i, DtCol)) = Month(Date) - 1 And x(i, 3) = ContAreas(ii) And x(i, 7) = Category And x(i, 8) = InspType Then 'get Type Mismatch error
- 'Also,extra code to EXCLUDE current and previous month but includes ALL months prior ie if macro is run in Sept it only includes July and all prior months, needs to be dynamic
- 'If x(i, 3) = ContAreas(ii) And x(i, 7) = Category And x(i, 8) = InspType Then 'this works OK without the date code
- For iii = 1 To 9
- If IsEmpty(y(ii, Cols(iii))) Then y(ii, Cols(iii)) = 0
- If x(i, 4) Like WrkClss(iii) Then
- y(ii, Cols(iii)) = y(ii, Cols(iii)) + 1
- End If
- Next
- End If
- Next
- Next
- With ActiveSheet
- .[h5].Resize(6, 26) = y
- .Activate
- End With
- End Sub