Excel problem with my loops?!?

[markw996]

Hi,

I'm trying to select races from the quickpick lists using the "-5" & "-1" triggers in the Refresh Rate cell using build 1.1.0.48.

The problem is that when I run my code the whole spreadsheet freezes which I can only assume is because my DO UNTIL loops are not allowing BA to update the sheet and hence read my commands.

Can anyone shed any light on what I've done wrong and offer a way to resolve the issue?

Code: Select all

Sub cmdProgramBAGrids()

Sheets("Dashboard").Range("E18") = "Programming..."

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Dim lngRefreshRow As Long
Dim strQ As String
Dim strAQ As String
Dim strQandAQ As String

With Sheets("Betfair")
    lngRefreshRow = 2
    'set All the double grids (i.e. Win and Place together is a double grid) _
    Quick Pick lists to the first selection in the lists
    For i = 1 To intCount
        .Range("Q" & lngRefreshRow).Value = "-5"
        .Range("AQ" & lngRefreshRow).Value = "-5"
        strQandAQ = "Not Quote Quote"
        Do Until strQandAQ = ""
            DoEvents
            strQ = .Range("Q" & lngRefreshRow).Value
            strAQ = .Range("AQ" & lngRefreshRow).Value
            strQandAQ = strQ & strAQ
            DoEvents
        Loop
        lngRefreshRow = lngRefreshRow + 50
    Next i
   
    'reset row count
    lngRefreshRow = 2
   
    'set the grids to the correct races from the QP list
    Dim intWinCount As Integer
    Dim intPlaceCount As Integer
    intWinCount = 0 'no need to alter win grid as already set to top of QP list
    intPlaceCount = 1 'we need to alter place grid as its set to top of QP list
    'set win grids
    For i = 1 To intCount
        For w = 0 To intWinCount
            .Range("Q" & lngRefreshRow).Value = "-1"
            Do Until strQ = ""
                DoEvents
                strQ = .Range("Q" & lngRefreshRow).Value
                DoEvents
            Loop
        Next w
       
        'set place grids
        For p = 0 To intPlaceCount
            .Range("AQ" & lngRefreshRow).Value = "-1"
            Do Until strQ = ""
                DoEvents
                strAQ = .Range("AQ" & lngRefreshRow).Value
                DoEvents
            Loop
        Next p
            intWinCount = intWinCount + 2
            intPlaceCount = intPlaceCount + 2
            lngRefreshRow = lngRefreshRow + 50
    Next i
   
End With

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Sheets("Dashboard").Range("E18") = "Done!"

End Sub

Thanks,

Mark.

[jokerjoe]

I don't have my code in front of me but I think I use something like:

Code: Select all

Do Until .Range("Q" & lngRefreshRow).Value = Empty And .Range("AQ" & lngRefreshRow).Value = Empty
     DoEvents
Loop