Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
improvements in telling loop to stop
Hi, i have designed a loop statement that looks to a external workbook to retrieve data. This workbook(wb2) contains sheets named after years from 1992 to 2003. Each Worksheet contains information for about 120 different catergories. Within my original workbook(wb1) 15 or so of the catergories have there own worksheet, my code will then open up wb2 and the first sheet (1992) and return the data series for the particular catergory, it then moves on to next year (1993) and so on. At the moment i'm telling it exactly how to stop e.g loop until iCell = "2004" I would like to improve this to say something along the lines of if worksheet(myVariable called sYear) is nothing then exit the loop. I've been experimenting with Do ' start loop sYear = Ocell.Text ' this value will change on each loop wb2.Activate Set wsI = Worksheets(sYear) ''year to search for wsI.Activate ' make the intermediate sheet active Range("A3").Select wsI.Cells.Find(sIO).Select ' Set Ocell = Ocell.Offset(0, 1) ' go to next row in series Loop Until wsI Is Nothing Unfortunatly this effort only makes it as far as the line in red e.g it will loop until sYear = 2005 and then stops as no worksheet is called 2005 thus producing error and breaking the loop anyway. Can anyone see an easy approach around what i'm trying to do? thanks -- cereldine ------------------------------------------------------------------------ cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069 View this thread: http://www.excelforum.com/showthread...hreadid=535958 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
improvements in telling loop to stop
cereldine wrote:
Hi, i have designed a loop statement that looks to a external workbook to retrieve data. This workbook(wb2) contains sheets named after years from 1992 to 2003. Each Worksheet contains information for about 120 different catergories. Within my original workbook(wb1) 15 or so of the catergories have there own worksheet, my code will then open up wb2 and the first sheet (1992) and return the data series for the particular catergory, it then moves on to next year (1993) and so on. At the moment i'm telling it exactly how to stop e.g loop until iCell = "2004" I would like to improve this to say something along the lines of if worksheet(myVariable called sYear) is nothing then exit the loop. I've been experimenting with Do ' start loop sYear = Ocell.Text ' this value will change on each loop wb2.Activate Set wsI = Worksheets(sYear) ''year to search for wsI.Activate ' make the intermediate sheet active Range("A3").Select wsI.Cells.Find(sIO).Select ' Set Ocell = Ocell.Offset(0, 1) ' go to next row in series Loop Until wsI Is Nothing Unfortunatly this effort only makes it as far as the line in red e.g it will loop until sYear = 2005 and then stops as no worksheet is called 2005 thus producing error and breaking the loop anyway. Can anyone see an easy approach around what i'm trying to do? thanks Do ' start loop sYear = Ocell.Text ' this value will change on each loop wb2.Activate On Error Resume next Set wsI = Worksheets(sYear) ''year to search for On error Goto 0 If wsI Is Nothing Then Exit Do wsI.Activate ' make the intermediate sheet active Range("A3").Select wsI.Cells.Find(sIO).Select ' Set Ocell = Ocell.Offset(0, 1) ' go to next row in series Loop |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
improvements in telling loop to stop
Try This:
On error resume next your code here On error goto 0 that will bypass the error generated and should abort the loop due to wsl = nothing Die_Another_Day |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
improvements in telling loop to stop
Do ' start loop sYear = Ocell.Text ' this value will change on each loop wb2.Activate On Error Resume next Set wsI = Worksheets(sYear) ''year to search for On error Goto 0 If wsI Is Nothing Then Exit Do wsI.Activate ' make the intermediate sheet active Range("A3").Select wsI.Cells.Find(sIO).Select ' Set Ocell = Ocell.Offset(0, 1) ' go to next row in series Loop I tried the above and it didn't work as expected, the loop picks up the fact that sYear is blank and provides an error message stating an error has occurred, it then breaks the loop but also the sub as well. In my code i have some further things i would like to do after the loop which this method bypasses. Thanks tho I experimented with the resume next, on error goto 0 as you suggested die_a_d previously and this also causes bizarre results, it just causes the code to copy and paste a number of unrequired colmns before moving on. -- cereldine ------------------------------------------------------------------------ cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069 View this thread: http://www.excelforum.com/showthread...hreadid=535958 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
improvements in telling loop to stop
Do ' start loop
sYear = Ocell.Text ' this value will change on each loop wb2.Activate On Error Resume next Set wsI = Worksheets(sYear) 'year to search for if err.number = 9 then '9 is the error I get when trying to set an invalid name to worksheet ' Do whatever you like. Exit Do, Exit Sub else wsI.Activate ' make the intermediate sheet active On error Goto 0 Range("A3").Select wsI.Cells.Find(sIO).Select ' end if Set Ocell = Ocell.Offset(0, 1) ' go to next row in series Loop Does that solve your problem? Die_Another_Day |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
telling range when to stop. | Excel Programming | |||
Hot key to stop a LOOP | Excel Programming | |||
how to stop a loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming | |||
newbie: stop a FOR NEXT loop | Excel Programming |