stefanj
05-27-2019, 11:12 AM
Good day,
I am creating an Excel template, and I am trying to create a macro to insert a new row at row 27, looping until cells X22 and Y22 are the same value, and to have this macro trigger whenever a change is made to cell D22. Cell Y22 has a formula that will (hopefully) revise itself upward each time a new row is inserted.
The code I have right now is as follows:
Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range
Set KeyCells = Range("D22")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Sub DoWhile_Loop()
Do While Range("X22").Value <> Range("Y22").Value
Sub insertRowFormatFromAbove()
Worksheets(ActiveSheet.Name).Rows(27).Insert Shift:=xlShiftDown
End Sub
Loop
End Sub
End If
End Sub
However, when I try making a change to D22, I get a the "Compile Error: Expected End Sub" error message. Does anyone know what might be wrong and how to fix it?
Currently using Microsoft Visual Basic for Applications 7.1 and Microsoft Excel for Office 365 version 1904.
Thank you very much.
I am creating an Excel template, and I am trying to create a macro to insert a new row at row 27, looping until cells X22 and Y22 are the same value, and to have this macro trigger whenever a change is made to cell D22. Cell Y22 has a formula that will (hopefully) revise itself upward each time a new row is inserted.
The code I have right now is as follows:
Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range
Set KeyCells = Range("D22")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Sub DoWhile_Loop()
Do While Range("X22").Value <> Range("Y22").Value
Sub insertRowFormatFromAbove()
Worksheets(ActiveSheet.Name).Rows(27).Insert Shift:=xlShiftDown
End Sub
Loop
End Sub
End If
End Sub
However, when I try making a change to D22, I get a the "Compile Error: Expected End Sub" error message. Does anyone know what might be wrong and how to fix it?
Currently using Microsoft Visual Basic for Applications 7.1 and Microsoft Excel for Office 365 version 1904.
Thank you very much.