VBA code to get the week number from the specified date

Question

I am working with Excel 2010.

I want to convert a given date from format mm/dd/yyyyto formatWyy"weeknumber"

For example, it 4/10/2017will W1715, since this is week 15 of 2017.

Below is an image of an excel table I'm working on. I want to convert the dates in a column LT Verification - Planned Dateto the week number format mentioned above in a column LT Verification - Planned Week Numbers.

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

I wrote 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 an error in myCell.Offset(0, 1).Value = "W" & Right(Year(myCell.Value), 2) & Application.WorksheetFunction.WeekNum(myCell.Value)

Excel, . , , , , .

, . , . , .

, . , (A B Z...)

Answer 1

VBA , Format. "\Wyyww" - , , \ , W .

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

. "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

Answer 2

:

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

A1 , .

VBA

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

---

Edit

, , 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

, , .

---

2: .

:

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

lastRow = 20,

myRange.Address = "A1:AZ120"

, 1 AZ. , AZ, A,

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

, , , B , A. C B!

Answer 3

, 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:

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 can even set the formula and paste the value, for example,

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