Trouble with error handling in a For/Next Loop

John,

I'm not sure what you want to happen when you hit an error but if you want the code to continue and just disregard the error you can use
 On Error Resume Next
instead of clearing the error description. Err.Clear will just clear the error message once and the code will stop when the error appears a second time at the same line.

Try this code and see if it meets your requirements:
 

Dim xCel As Range, lft As Range, rgt As Range
    For Each xCel In [Range1]
        If Cells(xCel.Row, 6) = [TargetTail] And Cells(xCel.Row, [S1Codes].Column) > 0 Then
            ThisWorkbook.Activate
            Set lft = xCel.Offset(0, 1)
            Set rgt = xCel.End(xlToRight)
            Dim i As Long, f As Long
            For i = lft.Column To rgt.Column
                ThisWorkbook.Activate
                Dim str As String, dt As Date, sCode As Range
                ThisWorkbook.Activate   'T H I S   W O R K B O O K . A C T I V A T
                str = Cells(xCel.Row, i)
                'dt = cells(xCel.Row, i).Offset(0, [RoundOpenDates].Column - cells(xCel.Row, i).Column)
                dt = Cells(xCel.Row, [RoundOpenDates].Column)
                Set sCode = Cells(xCel.Row, [S1Codes].Column)
                If sCode = "" Then GoTo here
                'ActiveWindow.ActivateNextT

                On Error Resume Next
                Dim Seat As Range, dtRng As Range, targ As Range
                Set dtRng = Rows("5:5").Find(What:=dt, After:=Cells(5, 3), LookIn:=xlValues)
                Set Seat = Columns("C:C").Find(What:=str, After:=Cells(1, 3), LookIn:=xlValues)
                Set targ = Cells(Seat.Row, dtRng.Column)
                ActiveWindow.ScrollRow = targ.Row - 2
                If targ = "" Then
                    targ = sCode
                Else
                    targ = targ & " | " & sCode
                End If
                'Kansas:
                'Err.Clear
            Next
here:
        Else: End If
        ThisWorkbook.Activate

Select allOpen in new window

Regards,
Curt

Remove the comment ' from line

17:                'ActiveWindow.ActivateNextT

 if you're using this line. I commented out this line while testing.

Regards,
Curt

Hi Curt, thanks for posting. I'm beginning to wonder if there's just something weird going on with my computer. I've tried On Error Resume Next and that doesn't work. I keep getting an'Object variable or With block variable not set' on this line: Set targ = cells(Seat.Row, dtRng.Column).  I just can't understand why it won't honor the error ???

If i have to time to upload the 2 files I'l try it at home tonight. Check back in with you tomorrow.

Thanks,
John

I tried it at home and I get the same result. What is so special about an Object variable or With block variable not set' error that it forges ahead even with "On Error Resume Next" right in front of it.  I wish I could post the two workbooks but there's so much client data in it ...

:-(

I've seen a few things on line about this exact issue but none of them seem to address my particular situation, at least I can't figure out how to apply them. I just keep thinking that there is some simple way that I can keep my code as is and fix it so that the error handler keeps working as many times as it needs to. I don't know if it will help but here's the full code I'm using right now.
The code looks at a series of cells in the rows that meet certain criteria in Column DQ. What's happening is the row 266 has two cells with values that don't appear in the specified worksheet in the other workbook. It handles the first error and just moves on to the next cell in Row 266 in the calling workbook. There must be some way to reset the error handler so that it handles the second error the same as the first.  No?

I wish I could post the workbooks. I spent an hour earlier trying to strip the customer specific data out of it but then the functionality got lost.

Here's hoping you get the brainstorm that's been eluding me all day!

Thanks,
John

Sub CopySCodes()
'On Error Resume Next
Dim ws As String
ws = [TargetTail]
ActiveWindow.ActivateNext   'A C T I V E W I N D O W . A C T I V A T E N E X T
Sheets(ws).Activate
ThisWorkbook.Activate
Dim xCel As Range, lft As Range, rgt As Range
ThisWorkbook.Activate
    'For Each xCel In [ParsedSeatNumbers]
    For Each xCel In [DQ11:DQ266]   ''''' F O R   T E S T I N G   O N L Y ''''''''
    xCel.Select   ''''' F O R   T E S T I N G   O N L Y ''''''''
    ThisWorkbook.Activate   'T H I S   W O R K B O O K . A C T I V A T E
    If cells(xCel.Row, 6) = [TargetTail] Then
        If xCel.Offset(0, 1) = "" Then GoTo here
    Set lft = xCel.Offset(0, 1)
    Set rgt = xCel.End(xlToRight)
    'Range(lft, rgt).Select   ''''' F O R   T E S T I N G   O N L Y ''''''''
    If lft = "" Then GoTo here
    Dim i As Long, f As Long
    ''''rgt.Select
        For i = lft.Column To rgt.Column
        
        ThisWorkbook.Activate   'T H I S   W O R K B O O K . A C T I V A T E
         cells(xCel.Row, i).Select   ''''' F O R   T E S T I N G   O N L Y ''''''''
            Dim str As String, dt As Date, sCode As Range
            str = cells(xCel.Row, i)
            'dt = cells(xCel.Row, i).Offset(0, [RoundOpenDates].Column - cells(xCel.Row, i).Column)
            dt = cells(xCel.Row, [RoundOpenDates].Column)
            Set sCode = cells(xCel.Row, [S1Codes].Column)
            If sCode = "" Then GoTo here
            cells(xCel.Row, i).Select   ''''' F O R   T E S T I N G   O N L Y ''''''
            ActiveWindow.ActivateNext   'A C T I V E W I N D O W . A C T I V A T E N E X T
            ''''[A1] = str   ''''' F O R   T E S T I N G   O N L Y ''''''
            Dim Seat As Range, dtRng As Range, targ As Range
            Set dtRng = Rows("5:5").Find(What:=dt, After:=cells(5, 3), LookIn:=xlValues)
            ''''dtRng.Select   ''''' F O R   T E S T I N G   O N L Y ''''''
            Set Seat = Columns("C:C").Find(What:=str, After:=cells(1, 3), LookIn:=xlValues)
            ''''Seat.Select   ''''' F O R   T E S T I N G   O N L Y ''''''
            On Error GoTo Kansas
            Set targ = cells(Seat.Row, dtRng.Column)
            ''''targ.Select   ''''' F O R   T E S T I N G   O N L Y ''''''
            If targ = "" Then
            ActiveWindow.ScrollRow = targ.Row - 2
            targ = sCode
            Else
            targ = targ & " | " & sCode
            End If
Kansas:
        Next
here:
    Else: End If
    ThisWorkbook.Activate

    Next
    ActiveWindow.ActivateNext   'A C T I V E W I N D O W . A C T I V A T E N E X T
    
    ActiveSheet.UsedRange.Calculate
ThisWorkbook.Activate   'T H I S   W O R K B O O K . A C T I V A T E
End Sub

Select allOpen in new window

If you are still seeing errors with an On Error Resume Next in place (assuming it's not being cancelled afterwards), then I would guess you have your VBEditor set to break on all errors in the Options.

>>> If dtRng Is Nothing Then <<<

That's the little magic bullet is was hoping for!

Thanks, imnorie.

- John