SpecialCells

Thanks for the help of Norman and Dave at
http://msdn.microsoft.com/newsgroups...=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

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

--
HTH...

Jim Thomlinson


"Peter" wrote:

> Thanks for the help of Norman and Dave at
> http://msdn.microsoft.com/newsgroups...=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

Hi Peter,

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

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Peter" <Peter@discussions.microsoft.com> wrote in message news:1AFE770E-9E5A-4F7B-9490-50F14EADBBA6@microsoft.com...
> Thanks for the help of Norman and Dave at
>
http://msdn.microsoft.com/newsgroups...=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

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.

Peter wrote:
>
> Thanks for the help of Norman and Dave at
> http://msdn.microsoft.com/newsgroups...=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

--

Dave Peterson