从2个输入日期中获取所有weeknums并将它们放入数组中？ [英] Get all weeknums from 2 input dates and put them in an array?

问题描述

由于我在上一篇文章中提出了一个错误的问题，但仍做了很多改进（我已经在Excel中创建了一个Planning表，如果有人愿意，我很乐意分享），因此，我打算这样做：单元格B2：开始日期和单元格B3：结束日期

Since I asked a wrong question in the last post, but still improved a lot (I already created a Planning table in Excel, if someone want it I will be happy to share), here is what im trying do to: Cell B2: Start Date and Cell B3: End Date

示例：

B2- -> 11/03/2019

B3-> 22/04/2019

Example:
B2 --> 11/03/2019
B3 --> 22/04/2019

在此社区的帮助下，这是我到目前为止的代码

Here is my code so far with the help of this community

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）

Basically with my current code I would obtain an array with this ouput: arr(1, 2, 3, 4, 5, 6)

与此-> 请参阅日历

我想获取： arr（11，12，13，14，15，15，16，17）

推荐答案

使用 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

这篇关于从2个输入日期中获取所有weeknums并将它们放入数组中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！