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]

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

[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