Posts by cobber1418

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

    Code
    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, 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

    Hi KjBox , is this all I need?

    Code
    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, 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
    End If

    HI KjBox , I deleted a couple of lines and it worked perfectly. Thanks again, much appreciated.

    Code
    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, 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

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


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

    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

    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.

    Hi, I would like the below countifs formulas converted to VBA code if possible. See file attached. Thanks in advance.


    Code
    =COUNTIFS(Notices!$P$7:$P$10000,"SECA11",Notices!$G$7:$G$10000,"Yes",Notices!$F$7:$F$10000,"Yes",Notices!$J$7:$J$10000,">="&Notices!$B$5,Notices!$J$7:$J$10000,"<="&EOMONTH(Notices!$B$5,0))
    Code
    =COUNTIFS(Notices!$P$7:$P$10000,"SECA11",Notices!$I$7:$I$10000,">="&Notices!$B$5,Notices!$I$7:$I$10000,"<="&EOMONTH(Notices!$B$5,0))+COUNTIFS(Notices!$P$7:$P$10000,"SECA11",Notices!$I$7:$I$10000,"<"&EOMONTH(Notices!$B$5,-1),Notices!$G$7:$G$10000,"Yes",Notices!$J$7:$J$10000,">="&Notices!$B$5,Notices!$J$7:$J$10000,"<="&EOMONTH(Notices!$B$5,0))+COUNTIFS(Notices!$P$7:$P$10000,"SECA11",Notices!$G$7:$G$10000,"No",Notices!$I$7:$I$10000,"<"&EOMONTH(Notices!$B$5,-1))

    Hi KjBox , you created the below code for me previously and I would like it modified if possible. It uses Option Base 1.


    I only need one Const, Post Work Manual

    I need an extra WrkClss, SMOK


    See code below, thanks in advance.