Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling with Nested Loops
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling with Nested Loops
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 -- Regards, Tom Ogilvy "Ctal" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling with Nested Loops
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling with Nested Loops
Thanks Tom and Myrna
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with nested for loops | Excel Discussion (Misc queries) | |||
Help with nested for loops | Excel Worksheet Functions | |||
Help on nested loops | Excel Programming | |||
Nested loops?? | Excel Programming | |||
Error Handling and For-Next Loops | Excel Programming |