Posts by cobber1418

Solved

I have posted on another forum but haven't received an answer yet

Power Query dynamic previous months filter

Hi all, how can I create a dynamic filter for 'previous months' but excluding last month, ie if it's currently October I want to filter for August and all…

www.mrexcel.com

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

Power Query dynamic previous months filter

Hi all, how can I create a dynamic filter for 'previous months' but excluding last month, ie if it's currently October I want to filter for August and all…

www.mrexcel.com

Hi all, how can I create a dynamic filter for 'previous months' but excluding last month, ie if it's currently October I want to filter for August and all previous months. Must be dynamic so that in November it filters for September and all previous months.

Is it possible to change the below code to suit?

= Table.SelectRows(#"Sorted Rows", each Date.IsInPreviousMonth([Remedy Date Due]))

Cheers Cobber.

Thanks rory...I'll find another way, cheers

Hi, how do I add to the below 'Last Month' code to also filter for "Yes" in the same column.

Range("A1").AutoFilter Field:=10, Criteria1:=xlFilterLastMonth, Operator:=xlFilterDynamic 

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.

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

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 royUK , I tried yours but couldn't get it to work,,,,sorry

Hi KjBox , is this all I need?

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.

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.

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.

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

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

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.