本文介绍了从2个输入日期中获取所有weeknums并将它们放入数组中?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

限时删除!!

由于我在上一篇文章中提出了一个错误的问题,但仍做了很多改进(我已经在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并将它们放入数组中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

1403页,肝出来的..

09-06 15:39