VBA Excel: sliding date selection from a table
Solved
Lena_tkd
Posted messages
1
Status
Member
-
yg_be Posted messages 23437 Registration date Status Contributor Last intervention -
yg_be Posted messages 23437 Registration date Status Contributor Last intervention -
Hello,
I work with Excel 2013 and have a table with dates in column A and names in column B: https://www.cjoint.com/c/IGqjkE6reID
In D1, I've placed a formula that indicates a rolling date of 31 days (06/15/2019 if today is 07/16/2019, 03/10/2019 if today is 04/11/2019...).
I would like to know what VBA code could allow me to select the part of the table (dates and names) from the earliest date to the date less than the one in D1, ideally incorporating the formula in D1 into the code to free up cell D1; in other words, I would like to be able to select the table from the first row to the last containing information from 06/14/2019 if D1 shows the date "06/15/2019"; does anyone have a lead?? Any ideas are welcome!
Thank you in advance!
I work with Excel 2013 and have a table with dates in column A and names in column B: https://www.cjoint.com/c/IGqjkE6reID
In D1, I've placed a formula that indicates a rolling date of 31 days (06/15/2019 if today is 07/16/2019, 03/10/2019 if today is 04/11/2019...).
I would like to know what VBA code could allow me to select the part of the table (dates and names) from the earliest date to the date less than the one in D1, ideally incorporating the formula in D1 into the code to free up cell D1; in other words, I would like to be able to select the table from the first row to the last containing information from 06/14/2019 if D1 shows the date "06/15/2019"; does anyone have a lead?? Any ideas are welcome!
Thank you in advance!
1 answer
-
yg_be Posted messages 23437 Registration date Status Contributor Last intervention Ambassadeur 1 588
Hello, I suggest this:Option Explicit Sub lena() Dim dt As Date, cejour As Date, nlig As Long, fl As Worksheet Set fl = ActiveSheet cejour = Date dt = DateSerial(Year(cejour), Month(cejour) - 1, Day(cejour - 1)) nlig = 1 Do While fl.Cells(nlig, 1) < dt And IsDate(fl.Cells(nlig, 1)) nlig = nlig + 1 Loop If nlig < 2 Then nlig = 2 End If fl.Range(fl.Cells(1, 1), fl.Cells(nlig - 1, 2)).Select End Sub
