title: Adding rows in Excel with VBA code or keep lRow static
body: <p>I have a form I am entering data with.</p>
  
  <p>I need to "add" a row in Excel via VBA code and keep my <code>lRow</code>. </p>
  
  <p>I would love to keep the adding rows to the bottom of the sheet but I have a formula that reference a cell and if I add to it, the formula will return bad data.</p>
  
  <p>I need to add a row and keep the <code>lrow</code> or make the formula change as the row gets added to the end.</p>
  
  <p>My adding row to the end of the data table is:</p>
  
  <pre><code>lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
  </code></pre>
  
  <p>and my formula row is</p>
  
  <pre><code>.Cells(lRow, 21).Value = "=IF(A2&lt;"" "",A2&amp;TEXT(D2,""mmddyy""),"""")"
  </code></pre>
  
  <p>As I add data the formula returns bad data due to its static and the added row is does not referance <code>A2</code> anymore but <code>B2</code> ...and so on.</p>
  
  <p>How can I add a row and keep <code>lRow</code> in my code working, or how to make the formula work as I add a new line, it indexes to the next line and not static anymore?</p>
  
  <p>Thanks</p>