Hi, I use the below code to select the previous months data but when I ran the code in January it didn't select the December data. Any help will be much appreciated.
Previous month code
 cobber1418
 Thread is marked as Resolved.





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
CodeIf 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 If

Hi KjBox , I tried the below code and it ran without an error but still didn't count the data for Dec. Note, I deleted Category from the code, not required.
CodeIf 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, 8) = InspType Then 'Your code here If Year(x(i, DtCol)) = Year(Date) And Month(x(i, DtCol)) = Month(Date)  1 And x(i, 3) = ContAreas(ii) And x(i, 8) = InspType Then 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, 8) = InspType Then 'Your code here If Year(x(i, DtCol)) = Year(Date) And Month(x(i, DtCol)) = Month(Date)  1 And x(i, 3) = ContAreas(ii) And x(i, 8) = InspType Then End If End If

HI KjBox , I deleted a couple of lines and it worked perfectly. Thanks again, much appreciated.
CodeIf 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, 8) = InspType Then 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, 8) = InspType Then 'Your code here If Year(x(i, DtCol)) = Year(Date) And Month(x(i, DtCol)) = Month(Date)  1 And x(i, 3) = ContAreas(ii) And x(i, 8) = InspType Then End If

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

Hi KjBox , is this all I need?


Hi KjBox , it works.....I just needed to remove the last End If to suit my code and it worked, cheers

You're welcome

For the record.....when I ran the code in Feb, KJBox's code didn't count Jan.
I tried royUK's code in Feb and it counted Jan.....so it appears to work.

I have been using EOMONTH since it was introduced and never had an issue.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!