vba 从给定日期获取周数的VBA代码

VBA code to get week number from given date

提问by Olivia

I am working with Excel 2010.

我正在使用 Excel 2010。

I wish to convert a given date from the format mm/dd/yyyyto the format Wyy"weeknumber"

我希望将给定的日期从格式mm/dd/yyyy转换为格式Wyy"weeknumber"

For example, 4/10/2017would become W1715, since it is week 15 of 2017.

例如，4/10/2017将变为W1715，因为现在是 2017 年的第 15 周。

The below shown image is of the excel table I am working on. I want to convert the dates in column LT Verification - Planned Dateto the week number format mentioned above, in column LT Verification - Planned Week Numbers.

下面显示的图像是我正在处理的 excel 表。我想将列中的日期转换为上述列LT Verification - Planned Date中的周数格式LT Verification - Planned Week Numbers。

Edit: Because this is part of a larger VBA process, I need it to be in VBA, not a cell formula.

编辑：因为这是更大的 VBA 过程的一部分，我需要它在 VBA 中，而不是单元格公式。

I have written the following code:

我编写了以下代码：

Public Sub WeekNumbers()

Dim lastRow As Integer
    lastRow = Range("A1:AZ1").Find("*", , , , xlByRows, xlPrevious).Row

Dim myRange As Range
    Set myRange = Range("A1:AZ1" & lastRow)

Dim myCell As Range
    For Each myCell In myRange

myCell.Offset(0, 1).Value = "W" & Right(Year(myCell.Value), 2) & Application.WorksheetFunction.WeekNum(myCell.Value)**

 Next myCell

 End Sub

This code gives me error at myCell.Offset(0, 1).Value = "W" & Right(Year(myCell.Value), 2) & Application.WorksheetFunction.WeekNum(myCell.Value)

这段代码给了我错误 myCell.Offset(0, 1).Value = "W" & Right(Year(myCell.Value), 2) & Application.WorksheetFunction.WeekNum(myCell.Value)

Here I have a excel workbook which will be updated every week. So, each time it is updated, it runs a macro to import data from another file & perform the week number activity & create a pivot table.

在这里，我有一个 excel 工作簿，每周都会更新。因此，每次更新时，它都会运行一个宏以从另一个文件导入数据并执行周数活动并创建一个数据透视表。

So, the sheet name changes every week. Also, the column headers may be in different columns in different weeks. Also, the number of rows may also change every week.

因此，工作表名称每周都会更改。此外，不同周的列标题可能位于不同的列中。此外，行数也可能每周发生变化。

So, I need to specify column & row range dynamically based on that weeks data. And have the week numbers in the column based on the column header rather than the column name (A or B or Z...)

所以，我需要根据那几周的数据动态指定列和行范围。并根据列标题而不是列名称（A 或 B 或 Z ...）

采纳答案by A.S.H

In VBA, you can get your string by using the Formatfunction. "\Wyyww"is the format you are looking for, where \is used to escapethe interpretation of the first Wcharacter and to take it as a litteral.

在 中VBA，您可以使用该Format函数获取字符串。"\Wyyww"是您要查找的格式， where\用于转义第一个W字符的解释并将其作为字面值。

myCell.Offset(0,1).Value = Format(myCell.Value, "\Wyyww")

More

更多的

You have to setup correctly the range for your loop. If your dates are in some column with header "LT Verificiation - Planned Date", you can try this:

您必须正确设置循环的范围。如果您的日期在带有 header 的列中"LT Verificiation - Planned Date"，您可以尝试以下操作：

Dim ws As Worksheet
Set ws = ActiveSheet ' <-- you can change this into something explicit like Sheets(someIndex)...

Dim myCell As Range
Set myCell = ws.Rows(1).Find("LT Verificiation - Planned Date")
For Each myCell In ws.Range(myCell.Offset(1), ws.Cells(ws.Rows.Count, myCell.Column).End(xlUp))
    If IsDate(myCell.value) Then myCell.Offset(, 1).value = Format(myCell.value, "\Wyyww")
Next myCell

回答by Wolfie

This can be achieved easily with a cell formula:

这可以通过单元格公式轻松实现：

="W" & RIGHT(YEAR(A1),2) & WEEKNUM(A1)

Where A1can be replaced by the cell containing the date.

WhereA1可以由包含日期的单元格替换。

In VBA this is equivalent to

在 VBA 中，这相当于

With Thisworkbook.Sheets("Sheet1")
    .Range("A2").Value = "W" & Right(Year(.Range("A1").Value), 2) & Application.WorksheetFunction.WeekNum(.Range("A1").Value)
End With

---

---

Edit:

编辑：

To fill an entire range, you could loop over the cells, apply the VBA calculation as above.

要填充整个范围，您可以遍历单元格，应用上述 VBA 计算。

Dim myRange as Range
Set myRange = Thisworkbook.Sheets("Sheet1").Range("A1:A10")

Dim myCell as Range
For Each myCell in myRange
    myCell.Offset(0,1).Value = "W" & Right(Year(myCell.Value), 2) & Application.WorksheetFunction.WeekNum(myCell.Value)
Next myCell

There are many methods for finding the last row in a range, so I'll leave that to you if you don't know your range.

有很多方法可以找到 range 中的最后一行，所以如果你不知道你的 range，我会把它留给你。

---

---

Edit 2: in response to your error edit.

编辑 2：响应您的错误编辑。

You have used the following line to define your range:

您已使用以下行来定义您的范围：

Set myRange = Range("A1:AZ1" & lastRow)

Let's imaging you have lastRow = 20, you now have

让我们想象一下你拥有的lastRow = 20，你现在拥有

myRange.Address = "A1:AZ120"

Which is clearly wrong, you shouldn't have the 1after the AZ. Also I don't know why you've gone to column AZ, if all of your date data is in column A, you should use

这显然是错误的，你不应该1在AZ. 另外我不知道你为什么去 column AZ，如果你所有的日期数据都在 column 中A，你应该使用

Set myRange = Range("A1:A" & lastRow)

The loop you've implemented uses an offset, so the values in column Bare changed to reflect those in column A. You can't then set column Caccording to column B!

您实现的循环使用了偏移量，因此列B中的值会更改以反映 A列中的值。然后您不能C根据列设置列B！

回答by z32a7ul

I don't think you need VBA for this, try this formula:

我认为你不需要 VBA，试试这个公式：

=RIGHT(YEAR(A1),2)&WEEKNUM(A1)&"W"

Of course, if you insist on VBA, you can always turn Excel Formulas into VBA code. In this case:

当然，如果你坚持使用VBA，你可以随时将Excel公式转化为VBA代码。在这种情况下：

Dim rngInput As Range
Dim rngOutput As Range
With Application.WorksheetFunction
    rngOutput.Value = .Right(.Year(rngInput.Value), 2) & .Weeknum(rngInput.Value) & "W"
End With

Or you may even set the Formula, and Insert the Value, like this

或者你甚至可以设置公式，然后插入值，就像这样

Dim rngInput As Range
Dim rngOutput As Range
rngOutput.Formula = "=RIGHT(YEAR(" & rngInput.Address(False, False) & "),2)&WEEKNUM(" & rngInput.Address(False, False) & ")&""W"""
rngOutput.Value = rngOutput.Value