I have a sheet that have a range of dates in column A. I already found the way to get the last row with:
LastRow = Worksheets("TIME").Cells(Rows.Count, "A").End(xlUp).Row
Now I am trying to get specific dates. The range contains no weekends, but since the dates are proprietary, I could not use the WorkDay function to find what I need.
From the last date available, I am trying to get the date 1 year before (if the date is not available, pick the next available one).
What I did here was to use the date function and subtract 1 year..
day1Y = date(year(LastRow)-1,month(LastRow),day(LastRow))
To match, I transformed the date range into an array, and used a function do determine if it is in the array. If it is, get it, but if it is not, I don't know how to get the next available.
Dim DateArray() as Variant
Dim WantedDate1 as date
Dim WantedDate2 as date
DateArray() = Worksheets("TIME").Range("A2:A" & LastRow).Value
If IsInArray(day1Y) = True then
WantedDate1 = .Cells(1,LastRow).Value
From the last available date, I am trying to get the first date in the same year (if last date is 10/08/2015, it gets the first available date of 2015, according to the dates available in the range).
WantedDate2 = Year(.Cells(1,LastRow).Value)
I got the year of the last date, but again, I can't find the first date of that year.
Any help will be deeply appreciated.
Use a loop to increase the days 1 by 1 and test if it is the array on the go :
Option Explicit Sub DGMS89() Dim wsT As Worksheet Dim LastRow As Double Dim DateArray() As Variant Dim LastDate As Date Dim Day1y As Date Dim WantedDate1 As Date Dim WantedDate2 As Date Set wsT = ThisWorkbook.Sheets("TIME") LastRow = wsT.Cells(wsT.Rows.Count, "A").End(xlUp).Row DateArray() = wsT.Range("A2:A" & LastRow).Value LastDate = DateArray(UBound(DateArray, 1)) Day1y = DateAdd("yyyy", -1, LastDate) WantedDate1 = Day1y If IsInArray(WantedDate1) Then Else Do While Not IsInArray(WantedDate1) WantedDate1 = DateAdd("d", 1, WantedDate1) Loop End If WantedDate2 = DateSerial(year(LastDate), 1, 1) Do While Not IsInArray(WantedDate2) WantedDate2 = DateAdd("d", 1, WantedDate2) Loop End Sub