当前位置: 动力学知识库 > 问答 > 编程问答 >

excel - Getting specific dates from a date range in VBA

问题描述:

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.

Case 1:

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

End if

Case 2:

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
分享给朋友:
您可能感兴趣的文章:
随机阅读: