improvements in telling loop to stop

[cereldine]

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

 

[Bob Phillips]

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

 

[Die_Another_Day]

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

 

[cereldine]

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.

 

[Die_Another_Day]

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