VBA: Loop hangs up after one pass

[mburch2000]

I have three fields of data:
1. CaseID: need to Group by
2. Side: either L or R
3. AcctNum

My main Sub: CaptureGrpMacro determines the start(rowStart) and end(rowEnd) rows of each group. The hasLorR function determines if Side is L or R and if so turns flag to True. The Scenario function takes the Scen variable and places in ScenL or ScenR field based on Side being L or R. So if a group(CaseID) has both a Side of L and R, then whatever is in ScenR or ScenL needs to be copied to all rows within that group. This is done by the ProcessGrp function.

So if I Dim out the Call ProcessGrp function, then rowStart, rowEnd, hasL, hasR, ScenR, and ScenL is populated for each row. But when I run the Sub with ProcessGrp function, it processes the first group then hangs up on Row 118 and I have to hit Ctrl-Break to get out of loop. Can someone please take a look at my code and sample data? I can't figure out what is wrong. Thank you very much.

Mike

Option Explicit
Private Data_Class As String, Scen As String, ScenR As String, ScenL As String
 
Function FindLastRow(LastRow As Long)
Dim sht As Worksheet
 
Set sht = ThisWorkbook.Worksheets("GrpTest")
LastRow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
 
End Function
Sub CaptureGrpMacro()
'This Function determines the start & end of a CaseID Group
Dim rowStart As Integer, rowEnd As Integer
Dim HRow As Integer, LastRow As Long
Dim hasL As Boolean, hasR As Boolean
Dim Side As String, PrevSide As String, NextSide As String
 
Call FindLastRow(LastRow)
HRow = 10
 
rowStart = 0
rowEnd = 0
hasL = False
hasR = False
Scen = ""
ScenL = ""
ScenR = ""
 
For HRow = HRow To LastRow
    Side = Cells(HRow, 2).Value
    PrevSide = Cells(HRow - 1, 2).Value
    NextSide = Cells(HRow + 1, 2).Value
 
    If Side <> "C" Then
 
        If (PrevSide = "C") And rowStart = 0 Then
            rowStart = HRow
        End If
 
        Call Scenario(HRow, Side, ScenL, ScenR)
        Call hasLorR(HRow, Side, hasL, hasR)
 
        If (NextSide = "C") And rowEnd = 0 Then
            rowEnd = HRow
        End If
 
    End If
    Worksheets("GrpTest").Cells(HRow, 5).Value = rowStart
    Worksheets("GrpTest").Cells(HRow, 6).Value = rowEnd
    Worksheets("GrpTest").Cells(HRow, 7).Value = hasL
    Worksheets("GrpTest").Cells(HRow, 8).Value = hasR
 
    'If rowStart & rowEnd have been assigned, then reset them
    If rowStart <> 0 And rowEnd <> 0 Then
        Call ProcessGrp(HRow, rowStart, rowEnd, ScenL, ScenR, hasL, hasR)
        rowStart = 0
        rowEnd = 0
        hasL = False
        hasR = False
        Scen = ""
        ScenL = ""
        ScenR = ""
    End If
 
Next HRow
 
End Sub
Function hasLorR(HRow, Side, hasL, hasR)
'Sets hasL and hasR variables based on each row
 
    If Side = "L" Then
        hasL = True
    Else: Side = "R"
        hasR = True
    End If
 
End Function
Function Scenario(HRow, Side, ScenL, ScenR)
'Determines Scen variable based on AcctNum field
Dim AcctType As String, Z As String, x As String
 
'Sets Data Class variable
x = Worksheets("GrpTest").Cells(HRow, 1).Value
Data_Class = Left(x, 1)
 
'Sets AcctType variable
Z = Worksheets("GrpTest").Cells(HRow, 3).Value            'Account Number
AcctType = Left(Z, 2)
 
'Sets Scen variable
Select Case AcctType
    Case "QM": Scen = "QMEMOACT"
    Case "CC": Scen = "QMEMOACT"
    Case "BS": Scen = "FINACT"
    Case "IS": Scen = "FINACT"
End Select
 
'Populates Scenario for every row
    If Side = "L" Then
        ScenR = Scen
        Worksheets("GrpTest").Cells(HRow, 9) = ScenR               'Side = L
    Else: Side = "R"
        ScenL = Scen
        Worksheets("GrpTest").Cells(HRow, 10) = ScenL               'Side = R
    End If
 
End Function
Function ProcessGrp(HRow, rowStart, rowEnd, ScenL, ScenR, hasL, hasR)
Dim rng As Range
Dim DRow As Integer
 
    If rowStart < rowEnd And (hasL And hasR = True) Then
        DRow = 0
        For HRow = rowEnd To rowStart Step -1
            For DRow = rowStart To rowEnd
                Worksheets("GrpTest").Cells(DRow, 9) = ScenR
                Worksheets("GrpTest").Cells(DRow, 10) = ScenL
            Next DRow
        Next HRow
    End If
    DRow = 0
End Function
 

[Rabbit]

The thing that pops out first is that your last row variable isn't populated. Passing a variable into a function creates a new variable within the scope of that function. Setting the value within that function doesn't change the value of the variable outside that function. There might be other things wrong as well but that's the first one I noticed.

[mburch2000]

Thank you for your reply Rabbit. I'm not sure I understand. The LastRow variable is set in the beginning, or at least I believe it is. The rowEnd variable is also set in the CaptureGrp function, or at least I believe it is. So what are you referring too and where if I may ask. Thank you

[Rabbit]

Line 18 doesn't set the last row variable within the function capture group macro. It only sets a different last row variable that exists only within the find last row function. Once that function returns, that other last row variable disappears.

You can test this by using the debugger to step through the code.

[mburch2000]

Rabbit, THANK YOU!!! Your were right. The HRow variable was getting reset to rowStart of the 1st group in the ProcessGrp function, thus continuing to loop through the first group. I set HRow variable to a new variable in ProcessGrp and it worked. I really appreciate your help, I have been stuck on this for far too long. Have a great day!

[Rabbit]

No problem, good luck on the rest of your project.