Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Specialcells | Charts and Charting in Excel | |||
SpecialCells(xlLastCell) | Excel Programming | |||
SpecialCells(xlCellTypeLastCell) | Excel Programming | |||
SpecialCells(xlCellTypeFormulas) | Excel Programming | |||
AutoFilter /specialcells | Excel Programming |