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
Lena_tkd
The code works perfectly, thank you very much!!!
yg_be
Posted messages
23437
Registration date
Status
Contributor
Last intervention
1 588
>
Lena_tkd
Perfect, can you then mark the issue as resolved, via the gear icon to the right of the title?
Lena_tkd
I don't see a gear icon, so in the email I received, I clicked on "your question is resolved," but it didn't do anything more than redirect me to the forum.
yg_be
Posted messages
23437
Registration date
Status
Contributor
Last intervention
1 588
>
Lena_tkd
Okay, I mark it as resolved, thank you for trying.
Lena_tkd