title: Date Sequence using VBA
body: <p>SITUATION</p>
  
  <p>I have a sequence of date serial numbers, for example:</p>
  
  <pre><code>42519, 42526, 42533, 42540, 42547, 42554
  </code></pre>
  
  <p>in cells</p>
  
  <pre><code>B2, C2, D2, E2, F2, G2
  </code></pre>
  
  <p>respectively.</p>
  
  <p>Eventually there will be 52 date serial numbers, each one representing a Weekly Invoice Date.</p>
  
  <p>You will notice that each one has been incremented by 7 in a previous macro, which presented no problems.</p>
  
  <p>OBJECTIVE</p>
  
  <p>I need to convert these  date serial numbers into a format <code>"dd-mmm-yy"</code>, using VBA mentioned in <strong>Method 2</strong> below (as opposed to copying formulas manually).</p>
  
  <p>So let's say the first date number is <code>42519</code> in cell <code>B2</code>.</p>
  
  <p><strong>Method 1.</strong></p>
  
  <p>This method which converts Date Serial Number <code>42519</code> to <code>"dd-mmm-yy"</code> format  presents no problem to me but is long winded and involves formula copying manually:</p>
  
  <p>Using  expression:</p>
  
  <pre><code> Range("B3")="=TEXT(B2,""dd-mmm-yy"")"    ' returns 29-May-16 in cell B3
  </code></pre>
  
  <p>I can even use the expression:</p>
  
  <pre><code> Range("C3")="=TEXT(B2+7,""dd-mmm-yy"")"    'returns 05-Jun-16 in cell C3
  </code></pre>
  
  <p><strong>Method 2.</strong></p>
  
  <p>This has me stumped and I'm coming up against a brick wall. It was my idea to do something like the following and if I can get this to work I can go ahead and use a loop to generate 52 dates in a row of 52 cells (<code>C3</code>, <code>D3</code>, etc in the format <code>"dd-mmm-yy"</code>, each one incremented by 7 days over the period of a year):</p>
  
  <pre><code>Sub sbNumToText_01()
      Dim intAdd7 As Integer
      intAdd7 = 0
      Dim lngSerialDate As Long
      lngSerialDate = Range("C2").Value
      MsgBox lngSerialDate + intAdd7  'returns 42526, as expected
      Range("C3") = "=TEXT(lngSerialDate + intAdd7,""dd-mmm-yy"")"
  End Sub
  </code></pre>
  
  <p>Instead of cell <code>C3</code> displaying <code>"05-Jun-16"</code>, cell <code>C3</code> displays <code>#Name?</code> and the mini drop down error menu on the left of cell <code>C3</code> says <code>"The formula contains unrecognised text"</code>.</p>
  
  <p>Can any of you out there please explain how to use the <code>TEXT</code> function  incorporating variables? Or any other solution along these lines.</p>