title: how to capture cell address as a variable and use in VB code?
body: <p>Need a code snippet; if some kind guru could help, please.  I need to express the following cursor movement sequence in XL VBA.</p>
  
  <p>After entering a formula in cell A1 (Col-A is otherwise empty), I need to copy the formula to all cells in the range A1:AN, where N is the last row of the table.</p>
  
  <p>I recorded a macro to do the following (code below):</p>
  
  <p>1) enter the formula (in Cell A1)</p>
  
  <p>2) copy the formula</p>
  
  <p>3) go Right to B1</p>
  
  <p>4) go to the last populated cell in Col-B [using Ctrl+Down] (easiest way to find the last row)</p>
  
  <p>5) go Left to Col-A</p>
  
  <p>6) select all cells from current to A1</p>
  
  <p>7) paste the formula to the selection</p>
  
  <p>The part I need help with is a way to capture the cell address in step 5 as a variable so that I can use this macro on a series of files having a variable number of rows.</p>
  
  <p>Here is the recorded macro.  In this example, the last row in the table is 7952.</p>
  
  <pre><code>Sub test()
      ActiveCell.FormulaR1C1 = "=LEFT(RC[1],3)"
      ActiveCell.Select
      Selection.Copy
      ActiveCell.Offset(0, 1).Range("A1").Select
      Selection.End(xlDown).Select
      ActiveCell.Offset(0, -1).Range("A1").Select
      Range(Selection, Selection.End(xlUp)).Select
      ActiveCell.Offset(-7951, 0).Range("A1:A7951").Select
      ActiveCell.Activate
      ActiveSheet.Paste
  End Sub
  </code></pre>