SpecialCells

[Guest]

Thanks for the help of Norman and Dave at
http://msdn.microsoft.com/newsgroup...officedev-xl&lang=en&cr=US&sloc=en-us&m=1&p=1

Now, I have another follow up question about formula in Excel worksheet.

I got a "No Cells were found" error if I use Active Sheet.SpecialCells()
method to retrieve all formulae on a worksheet. I suspect that this error
happen because I try to retrieve "nothing" if a worksheet have NO formula at
all. However, how can I skip this worksheet and then go on to next
worksheets?

Thanks,
Peter

 

[Guest]

First you should check if there are any formulas something like this...

dim cell as range
dim rngFormulas as range

on error resume next
set rngFormulas = cells.specialcells(xlCellTypeFormulas)
on error goto 0

if rngformulas is nothing then
msgbox "Sorry, No formulas."
else
for each cell in rngformulas
msgbox cell.Address
next cell
end if

 

[David McRitchie]

Hi Peter,

if Cells.SpecialCells(xlCellTypeFormulas) is nothing then ....

--

 

[Dave Peterson]

Norman's code did skip to the next worksheet:

Public Sub Tester002()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set WB = Workbooks("Book1.xls") '<<==== CHANGE

For Each SH In WB.Worksheets
set rng = nothing '<-- added
On Error Resume Next
Set rng = SH.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng.Cells
'do someting. e.g.:
rCell.Interior.ColorIndex = 6
Next rCell
End If
Next SH

End Sub

You may want to post the code you actually used if this doesn't help.