Not sure why this code stops??

[chinchi81]

Hi,

I have the following code but it keeps on stopping at myHomeAwayCol = 18 and I am not sure why. Doe anyone have any ideas?

Cheers,
Phil

Sub PhilsCode2()

ChDir "E:\Football Betting"
    Workbooks.Open Filename:="E:\Football Betting\Football Results.xls"
    Windows("Belgium - Jupiler League.xls").Activate
    Sheets("2007-2008").Select
    Range("A3:AE308").Select
    Range("AE308").Activate
    Selection.Interior.ColorIndex = xlNone
    Selection.Font.Bold = False

On Error GoTo myEnd

Dim myRow As Long
Dim myValue As String

 myHomeAwayCol = 13
 myGoalsCol = 14
 myAll = 31
 myRow = 3
 
For myRow = 3 To 308 'number of rows to look @

    Select Case Cells(myRow, myHomeAwayCol)
        Case "H"
            If Cells(myRow, myGoalsCol).Value >= 2.5 And Cells(myRow, myAll).Value = "HHH" Then
                Call myHighlight(myRow)
            End If
            
            If Cells(myRow, myGoalsCol).Value >= 1 And Cells(myRow, myGoalsCol).Value <= 2 And Cells(myRow, myAll).Value = "HHH" Then
                Call myHighlight(myRow)
            End If
        Case "A"
            If Cells(myRow, myGoalsCol).Value >= -1.5 And Cells(myRow, myGoalsCol).Value <= 0 And Cells(myRow, myAll).Value = "AAA" Then
                Call myHighlight(myRow)
            End If
        End Select
Next

 On Error GoTo myEnd
 
 myHomeAwayCol = 18
 myGoalsCol = 19
 myAll = 31
 myRow = 3
 


For myRow = 3 To 308 'number of rows to look @

    Select Case Cells(myRow, myHomeAwayCol)
        Case "H"
            If Cells(myRow, myGoalsCol).Value >= 2.5 And Cells(myRow, myAll).Value = "HHH" Then
                Call myHighlight1(myRow)
            End If
            
            If Cells(myRow, myGoalsCol).Value >= 1.5 And Cells(myRow, myGoalsCol).Value <= 2 And Cells(myRow, myAll).Value = "HHH" Then
                Call myHighlight1(myRow)
            End If
        
            If Cells(myRow, myGoalsCol).Value >= 0 And Cells(myRow, myGoalsCol).Value <= 0.5 And Cells(myRow, myAll).Value = "HHH" Then
                Call myHighlight1(myRow)
            End If
        
        Case "D"
            If Cells(myRow, myGoalsCol).Value >= 0 And Cells(myRow, myGoalsCol).Value <= 0.5 And Cells(myRow, myAll).Value = "DDD" Or "ADD" Or "AAD" Then
                Call myHighlight1(myRow)
            End If
        
        Case "A"
           If Cells(myRow, myGoalsCol).Value >= 1.5 And Cells(myRow, myGoalsCol).Value <= 2 And Cells(myRow, myAll).Value = "AAA" Then
                Call myHighlight1(myRow)
            End If
            
            If Cells(myRow, myGoalsCol).Value >= -1.5 And Cells(myRow, myGoalsCol).Value <= 0 And Cells(myRow, myAll).Value = "AAA" Then
                Call myHighlight1(myRow)
            End If
        End Select
Next
 
 On Error GoTo myEnd

 myHomeAwayCol = 23
 myGoalsCol = 24
 myAll = 31
 myRow = 3
 
For myRow = 3 To 308 'number of rows to look @

    Select Case Cells(myRow, myHomeAwayCol)
        Case "H"
            If Cells(myRow, myGoalsCol).Value >= 0 And Cells(myRow, myGoalsCol).Value <= 0.5 And Cells(myRow, myAll).Value = "HHH" Then
                Call myHighlight2(myRow)
            End If
        
        Case "D"
            If Cells(myRow, myGoalsCol).Value >= -1.5 And Cells(myRow, myGoalsCol).Value <= -1 And Cells(myRow, myAll).Value = "DDD" Or "ADD" Or "AAD" Then
                Call myHighlight2(myRow)
            End If
        
        Case "A"
           If Cells(myRow, myGoalsCol).Value >= 2.5 And Cells(myRow, myAll).Value = "AAA" Then
                Call myHighlight2(myRow)
            End If
            
            If Cells(myRow, myGoalsCol).Value <= -1.5 And Cells(myRow, myAll).Value = "AAA" Then
                Call myHighlight2(myRow)
            End If
           
           If Cells(myRow, myGoalsCol).Value >= -1 And Cells(myRow, myGoalsCol).Value <= 0 And Cells(myRow, myAll).Value = "AAA" Then
                Call myHighlight2(myRow)
            End If
        End Select
Next
myEnd:

End Sub


Function myHighlight(myRow As Long)

    Range("A" & myRow & ":C" & myRow).Select
        With Selection.Interior
            .ColorIndex = 8
            .PatternColorIndex = xlAutomatic
            Selection.Font.Bold = True
        End With
        
    Range("J" & myRow & ":N" & myRow).Select
        With Selection.Interior
            .ColorIndex = 8
            .PatternColorIndex = xlAutomatic
            Selection.Font.Bold = True
        End With

    Range("AE" & myRow).Select
        With Selection.Interior
            .ColorIndex = 8
            .PatternColorIndex = xlAutomatic
            Selection.Font.Bold = True
        End With

End Function

Function myHighlight1(myRow As Long)
Range("A" & myRow & ":C" & myRow).Select
        With Selection.Interior
            .ColorIndex = 8
            .PatternColorIndex = xlAutomatic
            Selection.Font.Bold = True
        End With
        
    Range("O" & myRow & ":S" & myRow).Select
        With Selection.Interior
            .ColorIndex = 8
            .PatternColorIndex = xlAutomatic
            Selection.Font.Bold = True
        End With

Range("AE" & myRow).Select
        With Selection.Interior
            .ColorIndex = 8
            .PatternColorIndex = xlAutomatic
            Selection.Font.Bold = True
        End With

End Function

Function myHighlight2(myRow As Long)
Range("A" & myRow & ":C" & myRow).Select
        With Selection.Interior
            .ColorIndex = 8
            .PatternColorIndex = xlAutomatic
            Selection.Font.Bold = True
        End With
        
    Range("T" & myRow & ":X" & myRow).Select
        With Selection.Interior
            .ColorIndex = 8
            .PatternColorIndex = xlAutomatic
            Selection.Font.Bold = True
        End With

Range("AE" & myRow).Select
        With Selection.Interior
            .ColorIndex = 8
            .PatternColorIndex = xlAutomatic
            Selection.Font.Bold = True
        End With

End Function

[Added code tags~VP]

 

[Boller]

What error message do you get if you remove the two earlier lines that read "On Error GoTo myEnd" ?

 

[Von Pookie]

Have you tried:
- stepping through the code to see what is happening?
- removing the 'goto myend' error trapping to see if there is an error occurring and what it is?

 

[chinchi81]

Hi,

There is no change if i remove the "On Error GoTo MyEnd" line

 

[Boller]

Do you have a breakpoint on the line "myHomeAwayCol = 18" ?

If so, remove the breakpoint.