Error Handling fails on second iteration of For Loop

Question

I'm producing a score generator to calculate player points based on match results.

One of my procedures aims to find the team name in column 1, then offsets the number beside it as the predicted score.

I've created a loop which looks for the team name. However, due to the fact that home teams will be in a different column, I've added error handling which goes to the next iteration of the loop each time the team cannot be found. However, it errors out at the second iteration with the following error message:

I've added Err.Clear to the end of the loop but this has not helped.

Sub CalculateGoalsMD1()

countries(0) = "ecuador"
countries(1) = "netherlands"
countries(2) = "qatar"
countries(3) = "senegal"

predictions = "Round 1"
results = "WC Predictions"
crws = "Results"
md1 = "MD1 Predictions"

For Each wb In Application.Workbooks
    If wb.name Like results & "*" Then
       Set CorrectResults = Workbooks(wb.name)
    End If
Next wb

lastColumn = CorrectResults.Worksheets(crws).Cells(1, Worksheets(1).Columns.count).End(xlToLeft).Column + 1
lastrowResults = CorrectResults.Worksheets(crws).Cells(Rows.count, "A").End(xlUp).Row
lastrowMD1 = CorrectResults.Worksheets(md1).Cells(Rows.count, "A").End(xlUp).Row
lastrowPredictions = CorrectResults.Worksheets(predictions).Cells(Rows.count, "A").End(xlUp).Row

CorrectResults.Worksheets(crws).Cells(lastrowResults + 1, 1).Value = CorrectResults.Worksheets(1).Range("D3")

points = 0
    
        For ct = 0 To 3

        With CorrectResults.Worksheets(crws).Columns(1)
        
            On Error GoTo Handler

            Set gw1 = .Find(countries(ct), LookIn:=xlValues)
            
            CorrectCountryColumn = gw1.Column + 1
            CorrectCountryRow = gw1.Row
            
            
        End With
        
        With CorrectResults.Worksheets(md1).Rows(lastrowMD1)
        
            On Error GoTo Handler

            Set gw2 = .Find(countries(ct), LookIn:=xlValues)
            
            PredictedCountryColumn = gw2.Column
            
        End With
        
        If CorrectResults.Worksheets(md1).Cells(lastrowMD1, PredictedCountryColumn + 1).Value = _
        CorrectResults.Worksheets(crws).Cells(CorrectCountryRow, CorrectCountryColumn).Value And _
        (PredictedCountryColumn = 2 Or PredictedCountryColumn = 7 _
        Or PredictedCountryColumn = 12 Or PredictedCountryColumn = 17 _
        Or PredictedCountryColumn = 22 Or PredictedCountryColumn = 27 _
        Or PredictedCountryColumn = 32 Or PredictedCountryColumn = 37 _
        Or PredictedCountryColumn = 42 Or PredictedCountryColumn = 47 _
        Or PredictedCountryColumn = 52 Or PredictedCountryColumn = 57 _
        Or PredictedCountryColumn = 62 Or PredictedCountryColumn = 67 _
        Or PredictedCountryColumn = 72 Or PredictedCountryColumn = 77) Then
        points = points + 3
        
        ElseIf CorrectResults.Worksheets(md1).Cells(lastrowMD1, PredictedCountryColumn - 1).Value = _
        CorrectResults.Worksheets(crws).Cells(CorrectCountryRow, CorrectCountryColumn).Value And _
        (PredictedCountryColumn = 6 Or PredictedCountryColumn = 11 _
        Or PredictedCountryColumn = 16 Or PredictedCountryColumn = 21 _
        Or PredictedCountryColumn = 26 Or PredictedCountryColumn = 31 _
        Or PredictedCountryColumn = 36 Or PredictedCountryColumn = 41 _
        Or PredictedCountryColumn = 46 Or PredictedCountryColumn = 51 _
        Or PredictedCountryColumn = 56 Or PredictedCountryColumn = 61 _
        Or PredictedCountryColumn = 66 Or PredictedCountryColumn = 71 _
        Or PredictedCountryColumn = 76 Or PredictedCountryColumn = 81) Then
        points = points + 3
        
        Else
        points = points
        
        End If
        
        CorrectResults.Worksheets(predictions).Cells(lastrowMD1 + 1, 3).Value = points

        
Handler:

        Err.Clear
        
        points = points
        
        Next ct
   

End Sub

Note: the functionality otherwise works fine - all variables have been publicly declared so please ignore lack of declarations!

Your error handler shoud be outside of the loop. Try adding Resume Next after points=points and see what happens. — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Jun 17, 2022 at 12:52
@SpectralInstance CorrectCountryColumn = gw1.Column + 1 on the second iteration. This is when it's looking for Netherlands, which is in a different column. Note that Ecuador (countries(0)) is also in a different column, so the error handler worked for the first iteration. — Danny
– Danny, Commented Jun 17, 2022 at 14:56
Your error handlers should not only be outside the loop, it should be outside the main body of the procedure. Add Exit Sub just before End Sub. Move Handler & Err.Clear to after the Exit Sub, add a line of code below that telling it to resume... Resume Here add the Here: label to just before the points = points line. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Jun 20, 2022 at 8:43
It looks like you could avoid the error completely by checking If Not gw Is Nothing Then before trying to retrieve the column from it. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Jun 20, 2022 at 8:46

Spectral Instance · Accepted Answer · 2022-06-17 16:08:51Z

I don't believe you can avoid using the Resume statement:

    Sub CalculateGoalsMD1()
    
    countries(0) = "ecuador"
    countries(1) = "netherlands"
    countries(2) = "qatar"
    countries(3) = "senegal"
    
    predictions = "Round 1"
    results = "WC Predictions"
    crws = "Results"
    md1 = "MD1 Predictions"
    
    For Each wb In Application.Workbooks
        If wb.name Like results & "*" Then
           Set CorrectResults = Workbooks(wb.name)
           Exit For
        End If
    Next wb
    
    lastColumn = CorrectResults.Worksheets(crws).Cells(1, Worksheets(1).Columns.count).End(xlToLeft).Column + 1
    lastrowResults = CorrectResults.Worksheets(crws).Cells(Rows.count, "A").End(xlUp).Row
    lastrowMD1 = CorrectResults.Worksheets(md1).Cells(Rows.count, "A").End(xlUp).Row
    lastrowPredictions = CorrectResults.Worksheets(predictions).Cells(Rows.count, "A").End(xlUp).Row
    
    CorrectResults.Worksheets(crws).Cells(lastrowResults + 1, 1).Value = CorrectResults.Worksheets(1).Range("D3")
    
    points = 0
        
            For ct = 0 To 3
    
                With CorrectResults.Worksheets(crws).Columns(1)        
                    On Error GoTo Handler
                    Set gw1 = .Find(countries(ct), LookIn:=xlValues)
                    CorrectCountryColumn = gw1.Column + 1
                    CorrectCountryRow = gw1.Row
                End With
            
                With CorrectResults.Worksheets(md1).Rows(lastrowMD1)
                    On Error GoTo Handler
                    Set gw2 = .Find(countries(ct), LookIn:=xlValues)
                    PredictedCountryColumn = gw2.Column
                End With
            
                If CorrectResults.Worksheets(md1).Cells(lastrowMD1, PredictedCountryColumn + 1).Value = _
                CorrectResults.Worksheets(crws).Cells(CorrectCountryRow, CorrectCountryColumn).Value And _
                (IsNumeric(Application.Match(PredictedCountryColumn,Array(2,7,12,17,22,27,32,37,42,47,52,57,62,67,72,77),0))) Then
                points = points + 3
            
                ElseIf CorrectResults.Worksheets(md1).Cells(lastrowMD1, PredictedCountryColumn - 1).Value = _
                CorrectResults.Worksheets(crws).Cells(CorrectCountryRow, CorrectCountryColumn).Value And _
                (IsNumeric(Application.Match(PredictedCountryColumn,Array(6,11,16,21,26,31,36,41,46,51,56,61,66,71,76,81),0))) Then
                points = points + 3
            
            Else
                points = points
            
            End If
            
            CorrectResults.Worksheets(predictions).Cells(lastrowMD1 + 1, 3).Value = points
            
    Handler:
            Resume Here
    Here:
            points = points     
            Next ct
       
End Sub

Collectives™ on Stack Overflow

Error Handling fails on second iteration of For Loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related