Loop Still Loops Beyond The Upper Limit

[Ark68]

With the code posted below, I am trying to add 'mrow' number of blank ranges to my worksheet.

            rng_pda_end = Application.WorksheetFunction.Match("Facility Maintenance Activities", .Columns(1)) - 3
            pda_cnt = rng_pda_end - 13
            If pda_cnt < 24 Then
                mrow = 24 - pda_cnt
                For L2 = 1 To mrow
                    .Range("A" & rng_pda_end & ":Q" & rng_pda_end).Insert Shift:=xlDown
                Next L2
            End If

pda_cnt = 20 (a count of rows in a particular area of my worksheet). There has to be a minimum of 24 rows in this area. So, since 20 is less than 24 (pda_cnt < 24), we need to add enough rows to bring that area up to 24.
'mrow' represents the number of rows that need to be added to bring this area up to the minimum 24 rows. In this case, mrow = 4
The L2 loop adds these empty ranges (A:Q) at row rng_pda_end which = 33.

I expect the loop to end after 4 loops, but it keeps going beyond.

Thoughts?

 

[RoryA]

That will only loop 4 times if mrow is 4. How is this code being triggered?

 

[Ark68]

Hi Rory ...
mrow is equal to 4.

I'm not sure how to answer how it's being triggered. It's inline with a process in a module. I know, not likely very helpful. I'm manually stepping through the code, but I stopped when L2 = 52.

The full module:

Sub trnsrv_delete(frmservice As Object, index As Long)
    Application.ScreenUpdating = False
    Dim msrv_col As Long
    Dim cdrow As Double
    'Stop
   
    ridno = ws_master.Cells(srow, 1)
    cdrow = Application.WorksheetFunction.Match(ridno, ws_cd.Columns(1))
   
    mbevents = False
    L1 = index
    With frmservice
        .Controls("cbx_s" & L1 & "_rln").Value = False
        .Controls("cbx_s" & L1 & "_chg").Value = False
        .Controls("tb_s" & L1 & "_lwr").Value = ""
        .Controls("tb_s" & L1 & "_upr").Value = ""
        .Controls("cb_s" & L1 & "_crew").Value = ""
        .Controls("cb_s" & L1 & "_div").Value = ""
        .Controls("cb_s" & L1 & "_base").Value = ""
        .Controls("cb_s" & L1 & "_pitch").Value = ""
    End With
    mbevents = True
    'Stop
    If submit = False Then 'new
        'check data integrity
        For index = 1 To 8
            If index <> L1 Then 'bypass the empty frame of the service eliminated
                If frmservice.Controls("cbx_s" & index & "_rln").Value = False And frmservice.Controls("cbx_s" & index & "_chg").Value = False Then Exit For
                trn_srv_datachk frmservice, index
            End If
        Next index
        'submit data to thold (for sorting) & continuity check
        ws_thold.Range("AI1:AQ9").Clear
        For index = 1 To 8
            If index <> L1 Then
                If frmservice.Controls("cbx_s" & index & "_rln") = False And frmservice.Controls("cbx_s" & index & "_chg") = False Then Exit For
                update_srvctemp frmservice, index
            End If
        Next index
        'update core data
        'Stop
        update_srvccore cdrow
        'update userform based on sort (align by time)
        'Stop
        With frmservice
            mbevents = False
            For index = 1 To 8
                .Controls("cbx_s" & index & "_rln").Value = False
                .Controls("cbx_s" & index & "_chg").Value = False
                .Controls("tb_s" & index & "_lwr").Value = ""
                .Controls("tb_s" & index & "_upr").Value = ""
                .Controls("cb_s" & index & "_div").Value = ""
                .Controls("cb_s" & index & "_crew").Value = ""
                .Controls("cb_s" & index & "_base").Value = ""
                .Controls("cb_s" & index & "_pitch").Value = ""
                If index > 1 Then
                    .Controls("frm_service" & index).Visible = False
                End If
            Next index
            mbevents = True
        End With

        update_srvcufrm frmservice
        srvsnum = Application.WorksheetFunction.CountA(ws_thold.Range("AJ1:AJ8"))
        frmservice.Controls("cbt_s" & srvsnum & "_add").Enabled = True
        If srvsnum > 4 Then
            frmservice.Height = 479
            frmservice.Width = 713
        Else
            frmservice.Height = 288
            If srvsnum = 4 Then
                frmservice.Width = 713
            ElseIf srvsnum = 3 Then
                frmservice.Width = 540
            Else
                frmservice.Width = 366
            End If
        End If
        frmservice.Top = Application.Top + (Application.UsableHeight / 2) - (frmservice.Height / 2)
        frmservice.Left = Application.Left + (Application.UsableWidth / 2) - (frmservice.Width / 2)
       
        'update PDA Booking section
        update_bookpda
       
        'update PDA Services section
        With ws_master
            mbevents = False
            .Unprotect
            rng_pda_start = Application.WorksheetFunction.Match("ADD", .Columns(1)) + 1
            rng_pda_end = Application.WorksheetFunction.Match("Facility Maintenance Activities", .Columns(1)) - 3
            For L1 = rng_pda_end To rng_pda_start Step -1
                If .Cells(L1, 1) = ridno Then .Range("A" & L1 & ":Q" & L1).Delete Shift:=xlUp
            Next L1
            'activity area base = 24 rows (13-36)
            Stop
            rng_pda_end = Application.WorksheetFunction.Match("Facility Maintenance Activities", .Columns(1)) - 3
            pda_cnt = rng_pda_end - 13
            If pda_cnt < 24 Then
                mrow = 24 - pda_cnt
                For L2 = 1 To mrow
                    .Range("A" & rng_pda_end & ":Q" & rng_pda_end).Insert Shift:=xlDown
                Next L2
            End If
            .Protect
            mbevents = True
        End With
        update_srvcpda
       
    End If
    Application.ScreenUpdating = True
End Sub

 

[RoryA]

That's not a macro you can run directly, so how is it being started?

 

[Ark68]

It's called from a userform button click event.

 

[RoryA]

OK, and how are you determining that it is being called more times than the value of mrow? There is nothing I can see there that could cause that.

 

[Ark68]

I'm stepping through the code and it appears to be stuck in this loop. I gave up at 52 loops.
If we look at the loop code itself

Line 1 For L2 = 1 to mrow
Line 2 insert range
Line 3 Next L2

When I step through, it follows this sequence ... Line 1, 2, 3, 2, 3, 2, 3, 2, 3, 2, 3 ......
Somehow I feel it should be going back to line 1, but it's not.

 

[Ark68]

I'm the last person to try and explain why, but when I changed the variables in the loop, it worked as anticipated. I changed L1 to km, and mrow to daf2.

 

[MARK858]

L1 might confuse Excel as it is a cell reference as well, best to avoid naming variables in a way that resemble a cell reference.
You might get issues with daf2.

 

[Ark68]

Thanks Mark, good advice to take forward!