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.
Posts by cobber1418
-
-
Hi KjBox , it works.....I just needed to remove the last End If to suit my code and it worked, cheers
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 -
Hi royUK , I tried yours but couldn't get it to work,,,,sorry
-
Hi KjBox , is this all I need?
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 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 -
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 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.
CodeIf 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 , that's really great, it will save me heaps of work. Thanks so much.
-
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?
CodeIf 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 royUK , work perfectly, much appreciated.
-
Thanks royUK , I'll give them a go, cheers
-
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.
Code
Display MoreOption Base 1 Sub DateMonths() Dim x, y, Cols, ContAreas, WrkClss, i As Long, ii As Long, iii As Long Const Category As String = "Standard of Work" Const InspType As String = "Post Work Manual" Const DtCol As Long = 10 '// target date col ContAreas = Array("SECA11", "SECA12", "SECA13", "SECA14", "SECA15", "SECA16") Cols = Array(1, 4, 7, 10, 13, 16, 19, 22, 25) WrkClss = Array("UW", "PW", "VAC*", "MPWCAP", "MOD*", "LGC", "BES", "MPWA", "SMOK") x = Sheets("calculations").[a6].CurrentRegion ReDim y(1 To 6, 1 To 26) For i = 2 To UBound(x, 1) For ii = 1 To 6 'the below code gives TYPE MISMATCH ERROR If Year(x(i, DtCol)) = Year(Date) And Month(x(i, DtCol)) = Month(Date) And x(i, 3) = ContAreas(ii) And x(i, 7) = Category And x(i, 8) = InspType Then 'get Type Mismatch error '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 'get Type Mismatch error '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 'If x(i, 3) = ContAreas(ii) And x(i, 7) = Category And x(i, 8) = InspType Then 'this works OK without the date code For iii = 1 To 9 If IsEmpty(y(ii, Cols(iii))) Then y(ii, Cols(iii)) = 0 If x(i, 4) Like WrkClss(iii) Then y(ii, Cols(iii)) = y(ii, Cols(iii)) + 1 End If Next End If Next Next With ActiveSheet .[h5].Resize(6, 26) = y .Activate End With End Sub -
Hi royUK , yes I have done that but I would still like VBA if possible, cheers
-
Hi royUK , other people need to use it at work and it suits better.
I know it's a bit clunky so don't worry if it's a bit difficult, cheers.
-
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 , works perfectly. Much appreciated.
-
Hi KjBox , I tried attaching it to a workbook but I usually just use Personal so not sure if it worked.
-
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.
Code
Display MorePrivate Sub WorkPrograms() 'Work Programs Dim x, y, Cols, ContAreas, WrkClss, i As Long, ii As Long, iii As Long Const Category As String = "Standard of Work" 'NO LONGER EQUIRED********* Const InspType As String = "Post Work Manual" 'ONLY NEED THIS********* ContAreas = Array("SECA11", "SECA12", "SECA13", "SECA14", "SECA15", "SECA16") Cols = Array(1, 4, 7, 10, 13, 16, 19, 22) WrkClss = Array("UW", "PW", "VAC*", "MPWCAP", "MOD*", "LGC", "BES", "MPWA") 'NEED "SMOK" ADDED AFTER "MPWA"************ x = Sheets("calculations").[a6].CurrentRegion ReDim y(1 To 6, 1 To 23) For i = 2 To UBound(x, 1) For ii = 1 To 6 If x(i, 3) = ContAreas(ii) And x(i, 7) = Category And x(i, 8) = InspType Then For iii = 1 To 8 If IsEmpty(y(ii, Cols(iii))) Then y(ii, Cols(iii)) = 0 If IsEmpty(y(ii, Cols(iii) + 1)) Then y(ii, Cols(iii) + 1) = 0 If x(i, 4) Like WrkClss(iii) Then If x(i, 9) = "Pass" Then y(ii, Cols(iii)) = y(ii, Cols(iii)) + 1 Else y(ii, Cols(iii) + 1) = y(ii, Cols(iii) + 1) + 1 End If End If Next End If Next Next With ActiveSheet .[h5].Resize(6, 23) = y .Activate End With End Sub -
Excellent royUK . Works perfectly, cheers.