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