Posts by cobber1418

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

    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.

    Files

    • Oz Date.xlsm

      (17.83 kB, downloaded 20 times, last: )

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


    Code
    1. =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
    1. =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.


    Thanks royUK, that's not good because I need the report to be static for the Aug figures and some managers may review the report later in Sept or Oct, so when they open it the figures will change.


    Could you please use the attached file to show me what you mean by using a date to base the formula on, if it's not to much trouble, cheers.

    Hi, is it bad practice when using countifs (or any excel formula) to select the whole column ie A:A, or is it better to just select the actual range ie A2:A100000.


    I understand using a table will make it dynamic etc....just want to know the pros and cons for each method.


    Thanks.