title: Excel VBA cut and paste function with subscript out of range error
body: <pre><code>    Sub CutandPaste()
      With ActiveSheet
          Dim i As Long
          Dim Sheet1 As Worksheet
          Dim Sheet2 As Worksheet
  
          Set mainsheet = ActiveWorkbook.Sheets("Sheet1")
          Set subsheet = ActiveWorkbook.Sheets("Sheet2")
  
          endrow = mainsheet.Range("A" &amp; mainsheet.Rows.Count).End(xlUp).Row
  
              For i = 25 To endrow
                  If mainsheet.Cells(i, "J") &lt;&gt; "" Or mainsheet.Cells(i, "L") &lt;&gt; "" Or mainsheet.Cells(i, "N") &lt;&gt; "" Then
                      mainsheet.Range(Cells(i, "B"), Cells(i, "O")).Cut Destination:=subsheet.Range(Cells(i + 25, "B"), Cells(i + 25, "O")).Paste
                      mainsheet.Range(Cells(i, "B"), Cells(i, "O")).Delete '~~&gt; if you want to delete
                      i = i + 1
                  End If
              Next
      End With
  End Sub
  </code></pre>
  
  <p>I would like to write a program with the below function</p>
  
  <p>If Cell ("J" i) or Cell("L" i) or Cell("N" i) in Sheet1 is not empty where i equals to any integer</p>
  
  <p>Then  cut Cell("B" i) to Cell("O" i) in Sheet 1 to Cell("B" i+25) to Cell("O" i+25) in Sheet2 where i equals to any integer</p>
  
  <p>However,my code was said to be out of range.</p>
  
  <p>Why is it like so and how can i fix it?</p>
  
  <p>Many thanks</p>
  
  <pre><code>    Sub CutandPaste()
      With ActiveSheet
          Dim i As Long
          Dim Sheet1  As Worksheet
          Dim Sheet2 As Worksheet
  
          Set mainsheet = ActiveWorkbook.Sheets("Sheet1")
          Set subsheet = ActiveWorkbook.Sheets("Sheet2")
  
          endrow = mainsheet.Range("A" &amp; mainsheet.Rows.Count).End(xlUp).Row
  
          For i = endrow To 25 Step -1
  
                  If mainsheet.Cells(i, "J") &lt;&gt; "" Or mainsheet.Cells(i, "L") &lt;&gt; "" Or mainsheet.Cells(i, "N") &lt;&gt; "" Then
                      mainsheet.Range(mainsheet.Cells(i, "B"), mainsheet.Cells(i, "O")).Cut Destination:=subsheet.Range(subsheet.Cells(i + 56, "B"), subsheet.Cells(i + 56, "O")).Paste
                      mainsheet.Range(mainsheet.Cells(i, "B"), mainsheet.Cells(i, "O")).Delete '~~&gt; if you want to delete
  
                  End If
              Next
      End With
  End Sub
  </code></pre>
  
  <p>Latest Version:</p>
  
  <pre><code>        Sub CutandPaste()
      With ActiveSheet
          Dim i As Long
          Dim Sheet1  As Worksheet
          Dim Sheet2 As Worksheet
  
          Set mainsheet = ActiveWorkbook.Sheets("Sheet1")
          Set subsheet = ActiveWorkbook.Sheets("Sheet2")
  
          endrow = mainsheet.Range("A" &amp; mainsheet.Rows.Count).End(xlUp).Row
          Bendrow = subsheet.Range("B" &amp; mainsheet.Rows.Count).End(xlUp).Row + 1
  
          For i = endrow To 25 Step -1
                  If mainsheet.Cells(i, "J") &lt;&gt; "" Or mainsheet.Cells(i, "L") &lt;&gt; "" Or mainsheet.Cells(i, "N") &lt;&gt; "" Then
                      mainsheet.Range(mainsheet.Cells(i, "B"), mainsheet.Cells(i, "O")).Cut Destination:=subsheet.Cells(Bendrow, "B")
                      mainsheet.Range(mainsheet.Cells(i, "B"), mainsheet.Cells(i, "O")).Delete '~~&gt; if you want to delete
                      Bendrow = Bendrow + 1
                  End If
              Next
      End With
  End Sub
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/XlCKg.jpg" rel="nofollow noreferrer">MainSheet Data</a></p>
  
  <p><a href="https://i.stack.imgur.com/yn5b9.jpg" rel="nofollow noreferrer">SubSheet Data</a></p>