• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

Trouble with error handling in a For/Next Loop

The code I have here is kind of clunky (or as zorvek might put it: "What idiot wrote THIS code?" :-)) but it works quite well unless the string 'str' doesn't appear in Column C of the target worksheet. I can't figure out why the error handling doesn't work; the code just keps on going and then bugs on the line: 'Set targ = cells(Seat.Row, dtRng.Column)"

Actaually it does trap the first error occasionally, but then it always bugs on the second.

So how do I fix it?

Thanks,
John


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 GoTo Kansas
            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
Next

Open in new window

0
gabrielPennyback
Asked:
gabrielPennyback
1 Solution
 
Curt LindstromCommented:
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

Open in new window


Regards,
Curt
0
 
Curt LindstromCommented:
Remove the comment ' from line

17:                'ActiveWindow.ActivateNextT

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

Regards,
Curt
0
 
gabrielPennybackAuthor Commented:
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
gabrielPennybackAuthor Commented:
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 ...

:-(
0
 
gabrielPennybackAuthor Commented:
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

Open in new window

0
 
Rory ArchibaldCommented:
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.
0
 
NorieCommented:
If Find isn't succesful then it will return Nothing.

Nothing doesn't have a column.

You can check if a Find has been succesful like this.
 Set dtRng = Rows("5:5").Find(What:=dt, After:=cells(5, 3), LookIn:=xlValues)

If dtRng Is Nothing Then

      MsgBox "Date not found."
      Exit Sub
Else
    ' continue with rest of code
End If

Open in new window

0
 
gabrielPennybackAuthor Commented:
>>> If dtRng Is Nothing Then <<<

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

Thanks, imnorie.

- John
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now