I have a loop which loops through sheets and cells via the SpecialCells
Method. If I come to a sheet which is blank I want the code to exit the loop
and proceed onto the next sheet. The error handling below does not do this.
What changes do I need to make?

For Each sht In ThisWorkbook.Worksheets
sht.Activate
On Error Resume Next ' Added in event that sheet does not have
formulas
For Each rng In sht.UsedRange.SpecialCells(xlCellTypeFormulas)
If Err.Number < 0 Then
Exit For
Else
rng.Select
End If

Thanks

EM

Set a range object to the formulas and then check that range object to see if
it is nothing...

Dim rngFormulas

For Each sht In ThisWorkbook.Worksheets
sht.Activate
On Error Resume Next
set rngFormulas = sht.UsedRange.SpecialCells(xlCellTypeFormulas)
On Error goto 0

if not rngformulas is nothing then
For Each rng In rngformulas
rng.Select
next rng
end if
next sht
--
HTH...

Jim Thomlinson


"ExcelMonkey" wrote:

I have a loop which loops through sheets and cells via the SpecialCells
Method. If I come to a sheet which is blank I want the code to exit the loop
and proceed onto the next sheet. The error handling below does not do this.
What changes do I need to make?

For Each sht In ThisWorkbook.Worksheets
sht.Activate
On Error Resume Next ' Added in event that sheet does not have
formulas
For Each rng In sht.UsedRange.SpecialCells(xlCellTypeFormulas)
If Err.Number < 0 Then
Exit For
Else
rng.Select
End If

Thanks

EM

Thanks Jim, I added a "Set rngformulas = Nothing" directly after the End if
stmt. Otherwise the following will occur:

on first pass, the code loops throught Sheet1 which has formulas.
Accordingly, rngformulas has cell addreses (See below). When it loops to the
second sheet which is blank, rngformulas.Address(external:=true) has the
previous sheets formula addresses in it. This prevents the If NOT
rngFormulas Is Nothing stmt from working properly while looping through the
second sheet.

For Each sht In ThisWorkbook.Worksheets
sht.Activate
On Error Resume Next
Set rngFormulas = sht.UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If Not rngFormulas Is Nothing Then
For Each rng In sht.UsedRange.SpecialCells(xlCellTypeFormulas)
'Do something
Next
End if
'<<<<Need to add a Set rngformulas = Nothing here
Next

First Loop
?sht.Name
Sheet1
?rngFormulas.Address(external:=true
'[File.xls]Sheet1'!$C$2:$AB$2,$C$4:$E$4,$D$7:$AB$7,$C$8:$AB$8 ,$C$9:$E$9,$C$10:$AB$10,$C$12:$E$12,$G$14:$AA$14,$ D$15,$F$15:$AB$15,$E$16:$AB$16,$C$17:$AB$17,$D$20: $E$20,$G$20:$AB$20,$C$21:$AB$22,$D$25,$E$25:$AB$26 ,$C$27:$AB$27

Second Loop
?sht.Name
Sheet2
?rngFormulas.Address(external:=true
'[File.xls]Sheet1'!$C$2:$AB$2,$C$4:$E$4,$D$7:$AB$7,$C$8:$AB$8 ,$C$9:$E$9,$C$10:$AB$10,$C$12:$E$12,$G$14:$AA$14,$ D$15,$F$15:$AB$15,$E$16:$AB$16,$C$17:$AB$17,$D$20: $E$20,$G$20:$AB$20,$C$21:$AB$22,$D$25,$E$25:$AB$26 ,$C$27:$AB$27



"Jim Thomlinson" wrote:

Set a range object to the formulas and then check that range object to see if
it is nothing...

Dim rngFormulas

For Each sht In ThisWorkbook.Worksheets
sht.Activate
On Error Resume Next
set rngFormulas = sht.UsedRange.SpecialCells(xlCellTypeFormulas)
On Error goto 0

if not rngformulas is nothing then
For Each rng In rngformulas
rng.Select
next rng
end if
next sht
--
HTH...

Jim Thomlinson


"ExcelMonkey" wrote:

I have a loop which loops through sheets and cells via the SpecialCells
Method. If I come to a sheet which is blank I want the code to exit the loop
and proceed onto the next sheet. The error handling below does not do this.
What changes do I need to make?

For Each sht In ThisWorkbook.Worksheets
sht.Activate
On Error Resume Next ' Added in event that sheet does not have
formulas
For Each rng In sht.UsedRange.SpecialCells(xlCellTypeFormulas)
If Err.Number < 0 Then
Exit For
Else
rng.Select
End If

Thanks

EM