Error Handling with Nested Loops

[Ctal]

I've got a couple of nested loops. The inner loop does a 'find' on one
sheet. Based on the found cell, it loads some values into variables that
are used on another page. The problem is with errors. The 'find' item may
occasionally not exist. If this is the case, I need for the loop to
continue with the next iteration. I've tried several approaches to fix this
and still come up short. Any ideas appreciated, code posted below:

(Some code to determine intNumTreaties)

For Counter = 1 To intNumTreaty

Sheets("FY Prem").Select
Range("B3").Select
Range(Selection, Selection.End(xlToRight)).Select
intNoCols = Selection.Columns.Count


For Counts = 1 To intNoCols

Sheets("FY Prem").Select
Range("A3").Select
strCurDate = ActiveCell.Offset(0, Counts).Value
strCurTreaty = ActiveCell.Offset(Counter, 0).Value
strCurGroup = strCurDate & strCurTreaty


Sheets("Acct Summary Pull").Select

Columns("A:A").Select
Selection.Find(what:=strCurGroup).Activate

(Error can happen here, if so need to move to next Counts at
this point)

curFYPrem = ActiveCell.Offset(0, 4).Value
curRnPrem = ActiveCell.Offset(0, 5).Value
curFYComm = ActiveCell.Offset(0, 6).Value
curRnComm = ActiveCell.Offset(0, 7).Value

(Some code here to work with the variables on other sheets)

Next Counts

Next Counter

End Sub

 

[Tom Ogilvy]

Dim rng as Range
For Counter = 1 To intNumTreaty

Sheets("FY Prem").Select
Range("B3").Select
Range(Selection, Selection.End(xlToRight)).Select
intNoCols = Selection.Columns.Count


For Counts = 1 To intNoCols

Sheets("FY Prem").Select
Range("A3").Select
strCurDate = ActiveCell.Offset(0, Counts).Value
strCurTreaty = ActiveCell.Offset(Counter, 0).Value
strCurGroup = strCurDate & strCurTreaty


Sheets("Acct Summary Pull").Select

Columns("A:A").Select
set rng = Selection.Find(what:=strCurGroup)
if not rng is nothing then
rng.Select
curFYPrem = ActiveCell.Offset(0, 4).Value
curRnPrem = ActiveCell.Offset(0, 5).Value
curFYComm = ActiveCell.Offset(0, 6).Value
curRnComm = ActiveCell.Offset(0, 7).Value


End if ' if not rng is nothing
Next Counts

Next Counter

End Sub

 

[Myrna Larson]

YOu don't have to select worksheets and cells to work with them, and doing so
slows down your code a lot. I think I've cleaned it up without introducing
bugs. If not, one mechanism to handle the "not found" is shown. Another is to
use Application.MATCH to locate the value. It returns an error it the data
isn't found. I've shown that method at the end.

Dim FYPrem AS Worksheet
Dim Summary AS Worksheet
Dim Cell As Range

Set FYPrem = Worksheets("FY Prem")
Set Summary = Worksheets("Acct Summary Pull")

For Counter = 1 To intNumTreaty
intNoCols = FYPrem.Range("B3").End(XlToRight).Column - 1

For Counts = 1 To intNoCols
With FYPrem.Range("A3")
strCurGroup = .Offset(0, Counts).Value & .Offset(Counter, 0).Value
End With

With Summary
Set Cell = .Columns(1).Find(what:=strCurGroup)
If Cell Is Nothing Then

Else
curFYPrem = Cell.Offset(0, 4).Value
curRnPrem = Cell.Offset(0, 5).Value
curFYComm = Cell.Offset(0, 6).Value
curRnComm = Cell.Offset(0, 7).Value
(Some code here to work with the variables on other sheets)
End If
End With
Next Counts

Next Counter

~~~~~~~~~~~~~~~~~~~~~
2nd approach, using MATCH

Dim R As Variant 'this line would go at the top, with other Dim stmts

For Counts = 1 To intNoCols
With FYPrem.Range("A3")
strCurGroup = .Offset(0, Counts).Value & .Offset(Counter, 0).Value
End With

With Summary
R = Application.MATCH(strCurGroup, .Columns(1), 0)
If IsError(R) = False Then
curFYPrem = .Cells(R, 5).Value
curRnPrem = .Cells(R, 6).Value
curFYComm = .Cells(R, 7).Value
curRnComm = .Cells(R, 8).Value
(Some code here to work with the variables on other sheets)
End If
End With
Next Counts

 

[Ctal]

Thanks Tom and Myrna