Code runs in debug mode only.

Hi all,

the code below runs fine in debug mode but stops at:

If TARGET_CELL.Offset(0, COLUMN_COUNT - 1).Value = 0 Then

with error message Run-Time Error 91 Object Variable or with Block variable not set. When trying to run through in one go

Sub UPDATE_MASTER_SHEET()
Dim CELL As Range, TARGET_CELL As Range
Dim FIND_STRING As String, NEW_GROUP As String
Dim COLUMN_COUNT As Integer, GROUP_COLOUR As Integer, REPORTING_DAYS As Integer
Dim REPORTING_DATE As Date, DATA_DATE As Date, REQUIRED_DATE As Date

COLUMN_COUNT = Application.WorksheetFunction.CountA(Worksheets("Master Sheet").Range("14:14")) + 1
REPORTING_DATE = Worksheets("Master Sheet").Range("A14").Offset(0, COLUMN_COUNT - 1).Value + 7
DATA_DATE = Int(Application.WorksheetFunction.Min(Worksheets("Times Retention Log Report").Range("A:A")))
REPORTING_DAYS = REPORTING_DATE - DATA_DATE
REQUIRED_DATE = REPORTING_DATE - 28

If REPORTING_DAYS <> 28 Then
    iRET = MsgBox("Please re-run the Zoho data extract between " & REQUIRED_DATE & " 00:00:00 and " & REPORTING_DATE & "00:00:00.", vbOKOnly, "Error with Data Extract")
    Exit Sub
    Else
End If

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .StatusBar = False
End With

For Each CELL In Worksheets("Groupings").Range("Agent_List")
If CELL.Value = 0 Then GoTo NEXT_CELL
    FIND_STRING = CELL.Value
    NEW_GROUP = CELL.Offset(0, 18).Value

    Select Case CELL.Offset(0, 19).Value
            Case "Relegation"
                GROUP_COLOUR = 3
            Case "Promotion"
                GROUP_COLOUR = 4
    End Select

   With Sheets("Master Sheet").Range("A:A")
            Set TARGET_CELL = .Find(What:=FIND_STRING, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If TARGET_CELL.Offset(0, COLUMN_COUNT - 1).Value = 0 Then
                GoTo NEXT_CELL
                Else
                    On Error Resume Next
                        With TARGET_CELL.Offset(0, COLUMN_COUNT)
                            .Value = NEW_GROUP
                            .Interior.ColorIndex = GROUP_COLOUR
                        End With
                    If Err.Number = 91 Then
                    msg = MsgBox("Unable to locate " & FIND_STRING & " in Master Sheet.", vbCritical, "Error")
                    End If
                    On Error GoTo 0
            End If
        End With

NEXT_CELL:
GROUP_COLOUR = nvbnull
Next CELL

With Worksheets("Master Sheet")
    For Each CELL In .Range("MASTER_AGENTS")
        If CELL.Offset(0, COLUMN_COUNT).Value = 0 Then
            CELL.Offset(0, COLUMN_COUNT).Value = CELL.Offset(0, COLUMN_COUNT - 1).Value
            Else
        End If
    Next CELL

    For Each CELL In .Range("MASTER_AGENTS")
        If CELL.Offset(0, COLUMN_COUNT).Value = 0 Then
            CELL.Offset(0, COLUMN_COUNT).Interior.ColorIndex = 16
            CELL.Offset(0, COLUMN_COUNT).Borders(xlEdgeRight).Weight = xlMedium
            Else
            CELL.Offset(0, COLUMN_COUNT).Borders(xlEdgeRight).Weight = xlMedium
        End If
    Next CELL
        
End With

With Worksheets("Master Sheet")
    .Range("A14").Offset(0, COLUMN_COUNT).Value = .Range("A14").Offset(0, (COLUMN_COUNT - 1)).Value + 7

    With .Range("A14").Offset(0, COLUMN_COUNT)
         .Borders(xlEdgeTop).Weight = xlMedium
         .Borders(xlEdgeRight).Weight = xlMedium
    End With

.Select
.Range(.Range("A4").Offset(0, COLUMN_COUNT - 1), .Range("A4").Offset(8, COLUMN_COUNT - 1)).Select
Selection.AutoFill Destination:=.Range(.Range("A4").Offset(0, COLUMN_COUNT - 1), .Range("A4").Offset(8, COLUMN_COUNT))

End With

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .StatusBar = False
End With
End Sub

any idea's what maybe causing this at all please?

EDIT: it's ok solved the issue grr was an error in the TARGET_CELL.Offset(0, COLUMN_COUNT - 1).Value

Hi Kramxel,

I've seen this when opening other files and it simply takes some OS time to perform the action. When you step through the code, there is time for the OS to do the work but when the code runs, lines of code fire before they are supposed to. To allow the OS time to do its work I've added some DoEvents code in my code which allows these other things to happen before trying to fire more lines of code. I use a lot of DoEvents when using SendKeys in my VBA code.

All the above being said, I haven't examined your code fully to see where a DoEvents might be needed (if at all), but the question sounds like this answer might be correct.