如果某一特定工作日(如星期五)不开始，如何将WeekNum展期

Question

我目前正在处理一个excel宏，它生成每周数据。我必须准备多份报告，如果我的每周开始日期不同，例如，如果一份报告，我的周开始日是“星期五”，而对于其他报告，周开始日是“星期一”

现在，我要分几个步骤来完成这个任务：

，

1. ，首先，我从excel源获取所有数据，并添加一个公式，以便在特定的一周内获得所有记录。我把“星期五”作为我一周中的第一天，
2. 按降序排列记录，得到每个AZ列的唯一值。这样我就得到了每周的最后一个记录，这就是我所看到的。

我为此使用的代码如下：

    Range("Data").AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Range("$A$1:$A$2"), _
    CopyToRange:=Range("$BB$4:$BD$4")

    FilterDataLastRow = Cells.Find(What:="*", _
    After:=Range("BA999999"), _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row

    ' Sort data in descending order of date
     ' Range("WeeklyFilteredData").Sort Key1:=Range("$BB$4:$BB$999999"), Order1:=xlDescending, Header:=xlYes
       Range("AW4:BE999999").Sort Key1:=Range("BC4:BC999999"), order1:=xlDescending, Header:=xlYes

    ' Assign Unique Key for each record row. We are using RowNum for same
        Range("BA5:BA" & FilterDataLastRow).Formula = "=ROW(RC[-2])"

    ' Assign SearchKey to filter Out all the data belonging to same week
        Range("AZ5:AZ" & FilterDataLastRow).Formula = "=(TEXT(RC[3],""yyyy""))&(TEXT(WEEKNUM(RC[3],15),""00""))"

    ' Get all data in User View
         Range("A5:A" & FilterDataLastRow).Formula = "=VLOOKUP(RC[51],C[51]:C[55],2,FALSE)"
         Range("B5:B" & FilterDataLastRow).Formula = "=VLOOKUP(RC[50],C[50]:C[54],3,FALSE)"
         Range("C5:C" & FilterDataLastRow).Formula = "=VLOOKUP(RC[49],C[49]:C[53],4,FALSE)"
         Range("E5:E" & FilterDataLastRow).Formula = "=VLOOKUP(RC[47],C[47]:C[51],5,FALSE)"

         Cells.RemoveDuplicates Columns:=Array(1)

​

​

在WEEKNUM 53之前，这是非常好的工作。2020年1月开始于周三，这被认为是一个WEEKNUM "1“，这对我的报告是不正确的。

目前，我的输出如下所示：

​

​

我需要修改我的代码，以跳过12/31/2019 (以红色突出显示)的数据，因为这些数据将作为一周的一部分计算，该周将于2020年1月1日结束。

请建议一种更好的方法将我的代码更新为enter code here

更新:2020年1月7日应答

我想出了一个方法来达到我的最终结果。但我知道仍有更好的方法来做同样的事情，因此我将这个问题保留下来，以便采取更好的办法。

以下是我所做的工作: 1.从给定日期检索月、日和工作日

Range("AW5:AW" & FilterDataLastRow).Formula = "=MONTH(RC[6])"
Range("AX5:AX" & FilterDataLastRow).Formula = "=DAY(RC[5])"
Range("AY5:AY" & FilterDataLastRow).Formula = "=WEEKDAY(RC[4],16)"

1. 现在添加了一个for循环。我试图在代码中的注释中解释我的每一步。

对于I=5 To FilterDataLastRow‘检查月份=1和日的记录，如果范围(“AW”& i).Value =1和范围(“AX”& i).Value <7，则PrevYear = CurrYear -1 PrevYearLastDay = CurrYear -1 PrevYearLastDay = "12/31/“和PrevYear范围(”AV“& i).Value =PrevYearLastDay=CurrYear=Range(”BC“&I).Value=CurrYear-1 PrevYearLastDay=”12/31/“&PrevYear Range(”AV&I“).Value=PrevYearLastDay。‘得到前一年12月31日的工作日范围(“AU”& i).Value =“=工作日( If 1，16)”’计算新一周开始的剩余天数DaysRemForNewWeek =8范围(“AU”& i).Value‘计算当前年度范围的第一个星期五的日期(“AT”& i).Value = PrevYearLastDay + DaysRemForNewWeek’比较第一个星期五之前的所有日期，以及从去年开始的WeekNum，如果这些日期是这样的话。范围(“BC”& i).Value < Range("AT“& i).Value (”AZ“& i).Formula =”=“(文本(RC-4)，“yyyy”)和(文本(WEEKNUM(RC-4，16)，“00”)“其他范围(”AZ“& i).Formula = "=(TEXT(RC3，”yyyy“))和(文本(WEEKNUM(RC 3，16)，”00“)”如果其他范围(AZ& i).Formula =“=(TEXT(RC3)，“yyyy”)&(文本(WEEKNUM(00 3，16)，“00”)“如果下一个i结束”

Answer 1

我想出了一个方法来达到我的最终结果。但我知道仍有更好的方法来做同样的事情，因此我将这个问题保留下来，以便采取更好的办法。

以下是我所做的工作: 1.从给定日期检索月、日和工作日

Range("AW5:AW" & FilterDataLastRow).Formula = "=MONTH(RC[6])"
Range("AX5:AX" & FilterDataLastRow).Formula = "=DAY(RC[5])"
Range("AY5:AY" & FilterDataLastRow).Formula = "=WEEKDAY(RC[4],16)"

1. 现在添加了一个for循环。我试图在代码中的注释中解释我的每一步。

对于I=5 To FilterDataLastRow‘检查月份=1和日的记录，如果范围(“AW”& i).Value =1和范围(“AX”& i).Value <7，则PrevYear = CurrYear -1 PrevYearLastDay = CurrYear -1 PrevYearLastDay = "12/31/“和PrevYear范围(”AV“& i).Value =PrevYearLastDay=CurrYear=Range(”BC“&I).Value=CurrYear-1 PrevYearLastDay=”12/31/“&PrevYear Range(”AV&I“).Value=PrevYearLastDay。‘得到前一年12月31日的工作日范围(“AU”& i).Value =“=工作日( If 1，16)”’计算新一周开始的剩余天数DaysRemForNewWeek =8范围(“AU”& i).Value‘计算当前年度范围的第一个星期五的日期(“AT”& i).Value = PrevYearLastDay + DaysRemForNewWeek’比较第一个星期五之前的所有日期，以及从去年开始的WeekNum，如果这些日期是这样的话。范围(“BC”& i).Value < Range("AT“& i).Value (”AZ“& i).Formula =”=“(文本(RC-4)，“yyyy”)和(文本(WEEKNUM(RC-4，16)，“00”)“其他范围(”AZ“& i).Formula = "=(TEXT(RC3，”yyyy“))和(文本(WEEKNUM(RC 3，16)，”00“)”如果其他范围(AZ& i).Formula =“=(TEXT(RC3)，“yyyy”)&(文本(WEEKNUM(00 3，16)，“00”)“如果下一个i结束”

Answer 2

给定一个特定的开始日，您对周的定义是什么？

如果它是从该日期开始的一年中的第一个完整的星期，那么您可以更容易地从VBA DatePart函数获得它。

DatePart("ww", myDate, vbFriday, vbFirstFullWeek)

如果您需要将它作为一个函数或公式的一部分放在您的工作表上，请将它用作一个UDF，而不是不那么灵活的工作表WEEEKNUM函数。或者，更好的方法是使用VBA year/wknum函数在vba中构造Format字符串，并将该字符串写入工作表。

例如：

Function yrWkNum(dt As Date) As String
    yrWkNum = Year(dt) & Format(DatePart("ww", dt, vbFriday, vbFirstFullWeek), "00")
End Function

​

​