Previous month code

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


    Code
    1. 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
  • Attach an example workbook. It's hard to know what your code is referring to.


    I would suggest something based on this


    Code
    1. If Month(Range("A1").Value) = Month(Application.WorksheetFunction.EoMonth(Date, -1)) Then MsgBox ("It's last month")
  • Hi royUK, I can't attach an example because it's from work. I assumed the problem is that the code substracts from a previous year, see below.


    If it isn't a simple change to the code I won't worry about it. Thanks anyway.


  • Possibly something along these lines

    Code
    1. If Month(Date) > 1 Then
    2. 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
    3. 'Your code here
    4. End If
    5. 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
    6. 'Your code here
    7. End If
    8. End If

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

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

    Code
    1. If Month(Date) > 1 Then
    2. 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
    3. 'Your code here
    4. 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
    5. End If
    6. 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
    7. 'Your code here
    8. 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
    9. End If
    10. End If
  • HI KjBox , I deleted a couple of lines and it worked perfectly. Thanks again, much appreciated.

    Code
    1. If Month(Date) > 1 Then
    2. 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
    3. End If
    4. 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
    5. 'Your code here
    6. 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
    7. End If
  • 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".

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi KjBox , is this all I need?

    Code
    1. If Month(Date) > 1 Then
    2. 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
    3. End If
    4. 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
    5. End If
  • Hi KjBox , it works.....I just needed to remove the last End If to suit my code and it worked, cheers

    Code
    1. If Month(Date) > 1 Then
    2. 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
    3. End If
    4. 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