in the following line (that works):
Selection.AutoFilter Field:=4, Criteria1:="<5/4/2024", Operator:=xlAnd
i would like to replace 5/4/2024 with EDATE(NOW(),120).
EDATE(NOW(),120) works in a cell as a formula (=EDATE(NOW(),120)) but it does not in a macro.
how can i use this in a macro?
Sub Select_Maturities()
'Selection.AutoFilter Field:=4, Criteria1:="<5/4/2024", Operator:=xlAnd ' This works
Selection.AutoFilter Field:=4, Criteria1:="<EDATE(NOW(),120)", Operator:=xlAnd ' NOT working!
End Sub
Excel/VB: Use of Variable in Filter Macro
Excel/VB: Use of Variable in Filter Macro
Last edited by Guest10 on 04 May 2014, 16:18, edited 1 time in total.
Re: Excel/VB Filter Macro Question
answering my own question:
Date = Now() + 365*10 ' >> 10 years from now "almost equivalent to" EDATE(NOW(),120)
NumberFormat = "mm/dd/yyyy"
Date = Format(Date, "mm/dd/yyyy")
MsgBox Date ' Testing...
'and this is the key:
Selection.AutoFilter Field:=4, Criteria1:="<" & Date, Operator:=xlAnd
p.s.: i discovered the use of Date for variable name messes up with the system clock. i changed this to Value:
Value = Now() + 365*10 ' >> 10 years from now "almost equivalent to" EDATE(NOW(),120)
...
...
...
Date = Now() + 365*10 ' >> 10 years from now "almost equivalent to" EDATE(NOW(),120)
NumberFormat = "mm/dd/yyyy"
Date = Format(Date, "mm/dd/yyyy")
MsgBox Date ' Testing...
'and this is the key:
Selection.AutoFilter Field:=4, Criteria1:="<" & Date, Operator:=xlAnd
p.s.: i discovered the use of Date for variable name messes up with the system clock. i changed this to Value:
Value = Now() + 365*10 ' >> 10 years from now "almost equivalent to" EDATE(NOW(),120)
...
...
...
Re: Excel/VB: Use of Variable in Filter Macro
and this is the exact solution:
Value = Format(Application.Evaluate("EDate(Now(), 120)"), "mm/dd/yyyy")
Selection.AutoFilter Field:=4, Criteria1:="<" & Value, Operator:=xlAnd
Value = Format(Application.Evaluate("EDate(Now(), 120)"), "mm/dd/yyyy")
Selection.AutoFilter Field:=4, Criteria1:="<" & Value, Operator:=xlAnd