VBA Getting a date variable always 3 months in the future

0
votes

I'm trying to find a way to get VBA to calculate a date 3 months in the future from the current date of the system (also as the 1st day of that future month).

This would be then used in filter below.

Workbooks(FN1).Worksheets("Sheet1").Range(Final1).AutoFilter Field:=18, Operator:= _
    xlFilterValues, Criteria2:=Array(1, "9/1/2017")

Basically what I need is to replace the "9/1/2017" with a variable that gets calculated.

Any help would be greatly appreciated. Thank you!

EDIT: Used below code in the end which works great. However, I just found out a problem which makes it a bit more complicated.

Workbooks(FN1).Worksheets("Sheet1").Range(Res).AutoFilter Field:=18, Operator:= _
    xlFilterValues, Criteria2:=Array(1, DateSerial(Year(Date), Month(Date) + 3, 1))

Seems like the filter needs to be set on multiple years, not just the current year. Recorded, it looks like this.

ActiveSheet.Range("$A$4:$X$2767").AutoFilter Field:=18, Operator:= _
    xlFilterValues, Criteria2:=Array(1, "9/1/2021", 1, "9/1/2020", 1, "9/1/2019", 1, _
    "9/1/2018", 1, "9/1/2017", 1, "9/1/2016", 1, "9/1/2015", 1, "9/1/2014", 1, "9/1/2013")

Problem is, the years will be variable as well (ex: Might not have 2013 in the data source next month but 2022 might be added). So problem gets more complicated as now I would need something that checks every year in the data source Range(Res).Column("R") and, I assume, would loop through all the years and check the 1st day of the month 3 months in the future.

Or rather is it possible to filter just on the month? So it just picks September (in this case) across all years?

3
vbaexcel
What date are you expecting from the calculated variable? – Mark Fitzgerald
Not sure I get your question. I need the variable so I can then use it in the code above instead of the typed in date. So whenever I run the code it would check what date is today, add 3 months to it and set it on the 1st day of the month. – Mazura_

3 Answers

1
votes

You can do it directly like:

Edit1: Based on your comment.

Criteria2:=Array(1, Format(DateAdd("m", 3, Date) - DatePart("d", Date) + 1, "m/d/yyyy"))
1
votes

Another way of doing it is to use DateSerial which returns a date given a specified year, month and day:

DateSerial(Year(Date), Month(Date) + 3, 1)

0
votes

If I understood you correctly, you want to get the date of the first of the month (that is 3 months from now) automatically. For that, you can use a combination of DateAdd and DateAdd function:

Dim myFirstoftheMonth As Date

myFirstoftheMonth = DateAdd("d", 1, WorksheetFunction.DateAdd(Date, 2))

afterwards, you can replace your hard-coded "9/1/2017" with myFirstoftheMonth in your Filter.