title: excel: insert row updating formulas
body: <p>I try to write a macro that on double click of a cell, inserts a new row below that cell with some formulas. The important thing for me is that if I double click the cell again, then the formulas of the previously inserted line are updated with the right indexes.</p>
  
  <p>For example, in the code below, double click A1 will insert the formula =B2+1 in line 2. Double clicking again should insert the same in line 2. But now line 2 shifter to line 3, so the formula in A3 should be =B3+1. </p>
  
  <p>Here is the code I have so far:</p>
  
  <pre><code>Option Explicit
  
  Const MYRANGE As String = "A:A"
  
  Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
      'If Sh.Name &lt;&gt; "Sheet1" Then Exit Sub
      If Target.Cells.Count &gt; 1 Then Exit Sub
      If Intersect(Target, Sh.Range(MYRANGE)) Is Nothing Then Exit Sub
      Cancel = True
      Target.Rows(2).Insert
      Dim newRow As Range
      Set newRow = Target.Rows(2)
      Dim rowIndex As Long
      rowIndex = newRow.row
      newRow.Cells(1, 1).Formula = "=B" &amp; rowIndex &amp; "+1"
  End Sub
  </code></pre>
  
  <p>UPDATE: Changing <code>Target.Rows(2).Insert</code> to <code>Target.Offset(1).EntireRow.Insert</code> solves the issue. Leaving the question open for explanations on what is Offset and how it differs from Rows (The property EntireRow does not exist for Rows(2))</p>