title: VBA code to get week number from given date
body: <p>I am working with Excel 2010. </p>
  
  <p>I wish to convert a given date from the format <code>mm/dd/yyyy</code> to the format <code>Wyy"weeknumber"</code></p>
  
  <p>For example, <code>4/10/2017</code> would become <code>W1715</code>, since it is week 15 of 2017.</p>
  
  <p>The below shown image is of the excel table I am working on. I want to convert the dates in column <code>LT Verification - Planned Date</code> to the week number format mentioned above, in column <code>LT Verification - Planned Week Numbers</code>.</p>
  
  <p>Edit: Because this is part of a larger VBA process, I need it to be in VBA, not a cell formula.</p>
  
  <p>I have written the following code:</p>
  
  <pre><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" &amp; lastRow)
  
  Dim myCell As Range
      For Each myCell In myRange
  
  myCell.Offset(0, 1).Value = "W" &amp; Right(Year(myCell.Value), 2) &amp; Application.WorksheetFunction.WeekNum(myCell.Value)**
  
   Next myCell
  
   End Sub
  </code></pre>
  
  <p>This code gives me error at <code>myCell.Offset(0, 1).Value = "W" &amp; Right(Year(myCell.Value), 2) &amp; Application.WorksheetFunction.WeekNum(myCell.Value)</code></p>
  
  <p>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 &amp; perform the week number activity &amp; create a pivot table.</p>
  
  <p>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.</p>
  
  <p>So, I need to specify column &amp; 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...)</p>
  
  <p><a href="https://i.stack.imgur.com/zNL2Y.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zNL2Y.jpg" alt="Excel Table"></a></p>