Help needed resolving Compile Error: Expected End Sub issue

[stefanj]

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.

[Paul_Hossler]

I'm guessing that it's because you have a Sub declared within a Sub declared within a Sub

I'm further guessing that you meant to call the second and third level subs

Option Explicit


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
        DoWhile_Loop    '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< call to sub
    End If
End Sub


Sub DoWhile_Loop()
    Do While Range("X22").Value <> Range("Y22").Value
        insertRowFormatFromAbove 
   '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< call to sub
    Loop
End Sub

Sub insertRowFormatFromAbove()
    Worksheets(ActiveSheet.Name).Rows(27).Insert Shift:=xlShiftDown
End Sub

[stefanj]

Thanks a lot for the help.

I'm running into another issue, in that I need (and neglected to account for initially ) a way to delete unneeded rows as well. I tried using an if...then setup based on whether X22 was larger or smaller than Y22 (if larger, insert a row, if smaller, delete a row) the DoWhile Loop subroutine, but I just got a Loop without Do error.

What is the syntax supposed to look like?

Sub DoWhile_Loop() If Range("X22").Value > Range("Y22").Value Then
insertRowFormatFromAbove '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< call to sub
ElseIf Range("X22").Value < Range("Y22").Value Then
deleteSpecificRow '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< call to sub
Else
End If
Loop
End Sub

Thank you very much.

[Paul_Hossler]

The macro recorder will usually give you pretty good syntax, but also records EVERYTHING you do, so you have to edit the result (remove .Select is the most common)

For deleting row, it's better to start with the last row and work your way to the top

You'll need to disable events before changing the sheet otherwise you'll trigger the event handler from inside the event handler (usually not a good thing)

I think that your loop will keep on going since I didn't see X22 or Y22 change

You can simplify a little

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Application.Intersect(
Range("D22")
, Target) Is Nothing Then Exit Sub

    Application.EnableEvents = False    '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    
    Do While Range("X22").Value <> Range("Y22").Value
        Rows(27).Insert Shift:=xlShiftDown
    Loop
    Application.EnableEvents = True  '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
End Sub

[stefanj]

Everything seems to be working more-or-less as I'd envisioned. Thank you very much for your help.