excel - 从 2 个输入日期获取所有周数并将它们放入数组中？

Question

由于我在上一篇文章中提出了一个错误的问题，但仍然改进了很多(我已经在 Excel 中创建了一个计划表，如果有人想要它，我会很乐意分享)，这是我尝试做的:< strong>单元格 B2:开始日期 和 单元格 B3:结束日期

示例:
B2 --> 11/03/2019
B3 --> 22/04/2019

这是我在这个社区的帮助下编写的代码

Option Explicit

Sub Sample()

Dim sDate As Date, eDate As Date
Dim NoOfWeeks As Long
Dim arr As Variant
Dim i As Long
Dim myCellToStart As Range
Set myCellToStart = Worksheets(1).Range("D4")

Dim myVar As Variant
Dim myCell As Range
Set myCell = myCellToStart

With Worksheets("Foglio1")
    sDate = .Range("B2")

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

    eDate = .Range("B3")
End With

If sDate = eDate Then
    NoOfWeeks = NoOfWeeks + 1
Else
    NoOfWeeks = NoOfWeeks + WorksheetFunction.RoundUp((eDate - sDate) / 7, 0)
End If

ReDim arr(1 To NoOfWeeks)
For i = 1 To NoOfWeeks
    arr(i) = i
Next i

End Sub

基本上，使用我当前的代码，我将获得一个包含以下输出的数组:arr(1, 2, 3, 4, 5, 6)

与此相关--> See Calendar

我想获得:arr(11, 12, 13, 14, 15, 16, 17)

Answer 1

使用 Application.WeekNum 会简单得多:

Option Explicit
Sub Test()

    Dim StartDate As Date, EndDate As Date

    With ThisWorkbook.Sheets("Foglio1") 'remember to fully qualify your ranges, including the workbook
        StartDate = .Range("B2")
        EndDate = .Range("B3")
    End With

    Dim StartWeek As Long, EndWeek As Long
    StartWeek = Application.WeekNum(StartDate, 2)
    EndWeek = Application.WeekNum(EndDate, 2)

    Dim arr
    Dim i As Long
    ReDim arr(StartWeek To EndWeek)
    For i = StartWeek To EndWeek
        arr(i) = i
    Next

End Sub