Attach an example workbook. It's hard to know what your code is referring to.
I would suggest something based on this
If Month(Range("A1").Value) = Month(Application.WorksheetFunction.EoMonth(Date, -1)) Then MsgBox ("It's last month")My code is much simpler than yours, but I can't make it fit exactly for you without seeing where you are using it.
Possibly something along these lines
If Month(Date) > 1 Then
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
'Your code here
End If
ElseIf Year(x(i, DtCol)) = Year(Date) - 1 And Month(x(i, DtCol)) = 12 And x(i, 3) = ContAreas(ii) And x(i, 7) = Category And x(i, 8) = InspType Then
'Your code here
End If
End IfI still think EOMONTH is the easier and better option.
You don't need line 6 of the code either. My amendment to your original code merely added an extra If..Then..End If statement to account for the current month being January. All you need to do is add your code for getting the previous month's data after each "Then".
You're welcome
I have been using EOMONTH since it was introduced and never had an issue.
Don’t have an account yet? Register yourself now and be a part of our community!