title: How to create a macro that can apply a formula to a dynamic range of cells, preferably using array?
body: <p><br/>
  I am completely new to VBA, so this task is a bit difficult for me but I bet it is easy for you guys. 
  I am trying to create a macro command that can automatically convert a series of dates from text to a date format that excel can recognize. This is a task which I regularly perform, so it would be very time saving to have a macro doing it for me. 
  Basically, I regularly download a time series of e.g. the historical price of a stock. The length of the time series varies every time. 
  Next I will need to convert the dates from the downloaded data to a format excel can recognize.</p>
  
  <p>To do so I use the following code:</p>
  
  <p><code>=DATE(RIGHT(B2,4),MONTH("1 "&amp;MID(B2,4,3)),LEFT(B2,2))</code></p>
  
  <p>in the cell adjacent to the first row of the date series.
  I then auto-fill this formula to the end of the series. </p>
  
  <p>I have created a macro that performs this task for me, using the following code:</p>
  
  <pre><code>Sub FacsetDates()
  ' FacsetDates Macro
  ' Turn Factset dates into excel format
  '
  ' Keyboard Shortcut: Ctrl+Shift+D
  
     ActiveCell.FormulaR1C1 = _
          "=+DATE(RIGHT(RC[-1],4),MONTH(""1 ""&amp;MID(RC[-1],4,3)),LEFT(RC[-1],2))"
      Selection.End(xlToLeft).Select
      Dim Lastrow As Long
      Lastrow = Cells(Rows.Count - 1, ActiveCell.Column).End(xlUp).Row
      Selection.End(xlToRight).Select
      Selection.AutoFill Destination:=ActiveCell.Range("A1:A" &amp; Lastrow - 1)
      ActiveCell.Range("A1:A" &amp; Lastrow - 1).Select
  End Sub
  </code></pre>
  
  <p>My problem is that this code only works if the date series start from row 2. 
  If the the series is inserted from row 1 the auto-fill will stop one row short and if the series start from row 3, the auto-fill will fill out one row too much (compared to the length of the data series)</p>
  
  <p>I would like a macro that works no matter which row the data series start. 
  E.g. I would like the macro to work even if the date series begin at B10. 
  I imagine that the solution is to set the data series as an array in VBA and then perform a loop that manipulate each string of text, and then finally paste the manipulated data in the adjacent column. 
  I have started producing the following code:</p>
  
  <pre><code>Sub FSdate()
  
   Dim arrMarks() As Long
   Lastrow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
   ReDim arrMarks(1 To Lastrow)
  
  Dim i As Long
   For i = LBound(arrMarks) To UBound(arrMarks)
      arrMarks(i) = ActiveCell
   Next i
  </code></pre>
  
  <p>In which I try to first define the array and its size, and then "copy" the string of text from the active cell (being the first row of the data series), but this code fails. 
  After having defined the array, I imagined to run a loop that use the DATE function from above to manipulate every single entry in the array. But my current skills in VBA falls short here, and I simply do not know how to proceed. </p>
  
  <p>Can anyone help create such a code?
  or even, do you guys have inputs to alternative ways of doing this task?
  Probably the initial code can be manipulated to work no matter which row the data series start. </p>
  
  <p>I hope somebody is able and willing to help me!</p>