excel - 将 weeknum 放入数组中？

Question

这是我想要做的:单元格 B2:开始日期和单元格 B3:结束日期

示例:
B2 --> 01/01/2019
B3 --> 01/03/2019

我想获取所有周数并将它们放入数组中

这是 Excel 单元格的图片

这是我到目前为止的代码

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim StartD As Date, EndD As Date 
Set wks = ThisWorkbook.Sheets("Foglio1")

Worksheets("Foglio1").Range("D2:XZ39").Clear 

StartD = Worksheets("Foglio1").Cells(2, 2) 
EndD = Worksheets("Foglio1").Cells(3, 2) 

我想循环遍历给定的两个日期:01/01/2019、01/03/2019获取周数并将其放入数组中(如果可能的话)

所以我的输出是:

Array(1, 2, 3, 4, 5, 6, 7, 8, 9) --> 从一月到三月有 9 周

Answer 1

如果您不想要日历周并且想要包括一周，即使还有一天，那么您可以尝试此操作。

逻辑

1. 它查找下一周的开始时间。因此默认周数为 1。
2. 循环并计算两个日期之间的天数(例如星期一，然后星期二等)，以最高者为准，返回该天数并将其添加到1。

也不需要循环和创建数组。您可以One Go创建顺序数组

代码

Option Explicit

Sub Sample()
    Dim WeeksCount As Long
    Dim CurCount As Long
    Dim countOfWeeks As Long
    Dim i As Long, tmpCount As Long

    Dim sDate As Date, eDate As Date

    sDate = DateSerial(2019, 1, 6)
    eDate = DateSerial(2019, 1, 8)

    If Weekday(sDate, vbMonday) <> 1 Then
        sDate = DateAdd("d", 7 - Weekday(sDate, vbMonday) + 1, sDate)
        WeeksCount = 1
    End If

    For i = 1 To 7
        CurCount = GetMeMyKindOfWeeksTotal(sDate, eDate, i)
        If tmpCount < CurCount Then tmpCount = CurCount
    Next i

    WeeksCount = WeeksCount + tmpCount

    Dim MyArray

    MyArray = Evaluate("Row(1" & ":" & WeeksCount & ")")

    If WeeksCount = 1 Then
        Debug.Print MyArray(1)
    Else
        For i = LBound(MyArray) To UBound(MyArray)
            Debug.Print MyArray(i, 1)
        Next i
    End If
End Sub

Private Function GetMeMyKindOfWeeksTotal(ByVal sDate As Date, ByVal eDate As Date, dy As Long)
    Dim j As Long
    Dim TotalDays As Long

    For j = sDate To eDate
        If Weekday(j) = dy Then
            TotalDays = TotalDays + 1
        End If
    Next

    GetMeMyKindOfWeeksTotal = TotalDays
End Function

各种测试

sDate = DateSerial(2019, 1, 6)
eDate = DateSerial(2019, 1, 8)

这会给你2

sDate = DateSerial(2019, 1, 1)
eDate = DateSerial(2019, 3, 1)

这将为您提供9

sDate = DateSerial(2019, 1, 1)
eDate = DateSerial(2019, 1, 1)

这会给你1

sDate = DateSerial(2019, 1, 1)
eDate = DateSerial(2019, 1, 8)

这会给你2