Hi KjBox , that's really great, it will save me heaps of work. Thanks so much.
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?
-
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.
Display MoreCode- Option 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!$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.
Display MoreCode- Private 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.
-
Hi royUK, last week I wanted everything counted prior to last month.....now I only want data counted during last month. I thought I could tweak your formula, but I can't get it to work.
-
-
Thanks royUK , that's perfect. Much appreciated.
-
Ordinary, thanks
-
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.
-
Thanks rory, excellent read, cheers,
-
Thanks rory. So the below formula is OK? Not quite sure what you mean by operator.
-
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.