Help with a For Next Loop

[BerkshireGuy]

I have a routine in Access that loops through an Excel workbook and
executes code base on the worksheet name.

If the worksheet name contains the word 'Stats' than that is a sheet I
want to read (the code).

However, I want to process the company level first because I am going
to need to compare the company stats with those of the individual
agency stats.

Here is the code I have:

bolCompanyDone = True
For Each oXlSheet In oXlBook.Worksheets

If InStr(1, oXlSheet.Name, "Stats", vbTextCompare) 0 Then
If bolCompanyDone = False Then
If InStr(1, oXlSheet.Name, "Overall Stats", vbTextCompare)

0 Then

'Subroutine
bolCompanyDone = True
Else ' Sheet Not The Company One - Get Next Sheet
End If
Else
If InStr(1, oXlSheet.Name, "Overall Stats", vbTextCompare)
= 0 Then
'Subroutine (dont want to process company here - its been
done already)
End If
End If
Else ' Name in Worksheet Not Stats
End If

Next oXlSheet

The problem I am having is that once the company is done - I need to
reset the For Next loop and basically start over since the Overall
Stats workbook can be anywhere in the sheets. If I dont restart the
For Next - then I may have already skipped worksheets that I do want to
process.

Now the easy way to solve this is to have two for next loops - One to
first handle the company and the second to handle all the others.

Is that my answer or is there a better way to do this?

Thanks!

[Wolf]

BerkshireGuy wrote:

I have a routine in Access that loops through an Excel workbook and
executes code base on the worksheet name.

If the worksheet name contains the word 'Stats' than that is a sheet I
want to read (the code).

However, I want to process the company level first because I am going
to need to compare the company stats with those of the individual
agency stats.

Here is the code I have:

bolCompanyDone = True
For Each oXlSheet In oXlBook.Worksheets

If InStr(1, oXlSheet.Name, "Stats", vbTextCompare) 0 Then
If bolCompanyDone = False Then
If InStr(1, oXlSheet.Name, "Overall Stats", vbTextCompare)

0 Then

'Subroutine
bolCompanyDone = True
Else ' Sheet Not The Company One - Get Next Sheet
End If
Else
If InStr(1, oXlSheet.Name, "Overall Stats", vbTextCompare)
= 0 Then
'Subroutine (dont want to process company here - its been
done already)
End If
End If
Else ' Name in Worksheet Not Stats
End If

Next oXlSheet

The problem I am having is that once the company is done - I need to
reset the For Next loop and basically start over since the Overall
Stats workbook can be anywhere in the sheets. If I dont restart the
For Next - then I may have already skipped worksheets that I do want to
process.

Now the easy way to solve this is to have two for next loops - One to
first handle the company and the second to handle all the others.

Is that my answer or is there a better way to do this?

Thanks!

It's not entirely clear what you are trying to do, but why don't you
just set oXlSheet to the Overall Stats sheet in the first place? If
you know the sheet you want to perform the subroutine on is always
called "Overall Stats" it should work.

Set oXISheet = oXlBook.Worksheets("Overall Stats")
' subroutine

You might need to wrap a for..next around the subroutine if you have
multiple companies, but without the subroutine code I can't really tell
what you're trying to do.

[BerkshireGuy]

Sorry about not being clear. I was in a crisis mode at the time. :)

There is a company sheet and then multiple agencies. I just had two
for next loops - One to first check for the company and then the second
to handle everything else. Each uses the same subroutine.

I was trying to get fancy with one for next loop with many if then
statements.

Thanks!

Wolf wrote:

BerkshireGuy wrote:

I have a routine in Access that loops through an Excel workbook and
executes code base on the worksheet name.

If the worksheet name contains the word 'Stats' than that is a sheet I
want to read (the code).

However, I want to process the company level first because I am going
to need to compare the company stats with those of the individual
agency stats.

Here is the code I have:

bolCompanyDone = True
For Each oXlSheet In oXlBook.Worksheets

If InStr(1, oXlSheet.Name, "Stats", vbTextCompare) 0 Then
If bolCompanyDone = False Then
If InStr(1, oXlSheet.Name, "Overall Stats", vbTextCompare)

0 Then

'Subroutine
bolCompanyDone = True
Else ' Sheet Not The Company One - Get Next Sheet
End If
Else
If InStr(1, oXlSheet.Name, "Overall Stats", vbTextCompare)
= 0 Then
'Subroutine (dont want to process company here - its been
done already)
End If
End If
Else ' Name in Worksheet Not Stats
End If

Next oXlSheet

The problem I am having is that once the company is done - I need to
reset the For Next loop and basically start over since the Overall
Stats workbook can be anywhere in the sheets. If I dont restart the
For Next - then I may have already skipped worksheets that I do want to
process.

Now the easy way to solve this is to have two for next loops - One to
first handle the company and the second to handle all the others.

Is that my answer or is there a better way to do this?

Thanks!

It's not entirely clear what you are trying to do, but why don't you
just set oXlSheet to the Overall Stats sheet in the first place? If
you know the sheet you want to perform the subroutine on is always
called "Overall Stats" it should work.

Set oXISheet = oXlBook.Worksheets("Overall Stats")
' subroutine

You might need to wrap a for..next around the subroutine if you have
multiple companies, but without the subroutine code I can't really tell
what you're trying to do.