Trying to write a formula inserting rows based on the difference between the ActiveCell and a cell above it, if the cell above is larger. The entire code works, except for the formula for "Diff". If I replace diff with a number, ie. "2", then the formula works fine. However the number of inserted rows need to vary based on the difference between the ActiveCell and the one above it. I even based the formula on non-explicit variables (IND, and INDabove) but the formula just keeps crashing on one specific line: ActiveCell.Resize(Diff).EntireRow.Insert
Below is my code...
Sub Indentured()
' Indentured Macro
' Keyboard Shortcut: Ctrl+W
Dim LR As Long
Dim IND As Range
Dim INDabove As Range
Dim Diff As Long
Application.ScreenUpdating = False
LR = Range("E" & Rows.Count).End(xlUp).Row
Set IND = ActiveCell
Set INDabove = ActiveCell.Offset(-1, 0)
Diff = ActiveCell.Offset(-1, 0).Value - ActiveCell.Value - 1
Range("E" & LR).Select
Do Until ActiveCell.Row = 2
If ActiveCell.Value < ActiveCell.Offset(-1, 0).Value Then
ActiveCell.Resize(Diff).EntireRow.Insert
End If
ActiveCell.Offset(-1).Select
Loop
Application.ScreenUpdating = True
End Sub
Bookmarks