Help with VBA 'Type Mismatch' error & date code

cobber1418 · Aug 31st 2021

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

Display More

**KjBox** · Sep 3rd 2021

The reason for the Type Mismatch Error is because of the values (1 to 10) that you have added to Row 5, they are part of the [a6].CurrentRegion and so get included when loading array x. The first row of array x should be the Header Row and the loop start at the second row of the array, but with those extra values they form the first row of the array and the 2nd row is the header row, the code then tries to get the Year of a header which is not a date hence the error.

To correct it you have 3 options

Remove the values in Row 5;
Move those values to Row 4 (making Row 5 empty
Change the code as shown below

Change

Code

For i = 2 To UBound(x, 1)

To

Code

For i = 3 To UBound(x, 1)

**KjBox** · Sep 3rd 2021

Note that even with 1 of the 3 options being used your code will not produce a result with your current test data, it is now Month 9 and the maximum Date in your data is Month 8 (except the last 2 rows and then the Year is 2020 not the current Year.

Therefore the date criteria is never met, ergo no result.

cobber1418 · Sep 3rd 2021

Hi KjBox , a trap for novices, I didn't actually need 1-10. I removed them and it's perfect.....thanks so much yet again.

In regard to my other question,

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

Do I just need to change -1 to -2 after the Month(Date) in the code below?

Code

 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

**KjBox** · Sep 3rd 2021

That would work as long as the current Month is not January or February, that would make Month(Date) - 2 to return -1 and 0 respectively, when what you need is 11 and 12.

To be fully dynamic you need to change

Code

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

to

Code

 If DateDiff("m", x(i, DtCol), Date) > 1 And x(i, 3) = ContAreas(ii) And x(i, 7) = Category And x(i, 8) = InspType Then

cobber1418 · Sep 3rd 2021

Hi KjBox , that's really great, it will save me heaps of work. Thanks so much.

**KjBox** · Sep 3rd 2021

You're welcome

Help with VBA 'Type Mismatch' error & date code

Participate now!

Share

Tags