How can I loop through all Formula in a workbook?

[Peter]

I tried Cells.SpecialCells(.... xlCellTypeFormulas...) but no luck.

Anyone have this code snippet?

Thanks,

[Norman Jones]

Hi Peter,

>I tried Cells.SpecialCells(.... xlCellTypeFormulas...) but no luck.

> Anyone have this code snippet?

Your "no luck" is far from explicit, but try something like:

'=============>>
Public Sub Tester001()
Dim rng As Range
Dim rCell As Range

On Error Resume Next
Set rng = 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

End Sub
'<<=============


---
Regards,
Norman


"Peter" <[email protected]> wrote in message
news:[email protected]...
>I tried Cells.SpecialCells(.... xlCellTypeFormulas...) but no luck.
>
> Anyone have this code snippet?
>
> Thanks,

[Bob Phillips]

This works for me

Dim cell As Range
For Each cell In Cells.SpecialCells(xlCellTypeFormulas)
Debug.Print cell.Address
Next cell


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter" <[email protected]> wrote in message
news:[email protected]...
> I tried Cells.SpecialCells(.... xlCellTypeFormulas...) but no luck.
>
> Anyone have this code snippet?
>
> Thanks,

[Norman Jones]

Hi Peter,

And if you wish to find all formulae in the workbook, try:

'=============>>
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
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
'<<=============


---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:%[email protected]...
> Hi Peter,
>
>>I tried Cells.SpecialCells(.... xlCellTypeFormulas...) but no luck.
>
>> Anyone have this code snippet?
>
> Your "no luck" is far from explicit, but try something like:
>
> '=============>>
> Public Sub Tester001()
> Dim rng As Range
> Dim rCell As Range
>
> On Error Resume Next
> Set rng = 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
>
> End Sub
> '<<=============
>
>
> ---
> Regards,
> Norman

[Dave Peterson]

I'd add one more line:

Set rng = nothing '<-- added
On Error Resume Next
Set rng = SH.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If there are no formulas on a worksheet, then rng won't change. And depending
on what's going to happen to those cells with formulas, it could be a problem.

And to the OP, Norman showed how to get a range with all the formulas. If
you're doing something like changing the fill color, you can do it all at
once--you don't need to loop through those cells.

Norman Jones wrote:
>
> Hi Peter,
>
> And if you wish to find all formulae in the workbook, try:
>
> '=============>>
> 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
> 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
> '<<=============
>
> ---
> Regards,
> Norman
>
> "Norman Jones" <[email protected]> wrote in message
> news:%[email protected]...
> > Hi Peter,
> >
> >>I tried Cells.SpecialCells(.... xlCellTypeFormulas...) but no luck.
> >
> >> Anyone have this code snippet?
> >
> > Your "no luck" is far from explicit, but try something like:
> >
> > '=============>>
> > Public Sub Tester001()
> > Dim rng As Range
> > Dim rCell As Range
> >
> > On Error Resume Next
> > Set rng = 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
> >
> > End Sub
> > '<<=============
> >
> >
> > ---
> > Regards,
> > Norman

--

Dave Peterson

[Norman Jones]

Hi Dave,

> I'd add one more line:

So would I!

Thank you!


---
Regards,
Norman



"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> I'd add one more line:
>
> Set rng = nothing '<-- added
> On Error Resume Next
> Set rng = SH.Cells.SpecialCells(xlCellTypeFormulas)
> On Error GoTo 0
>
> If there are no formulas on a worksheet, then rng won't change. And
> depending
> on what's going to happen to those cells with formulas, it could be a
> problem.
>
> And to the OP, Norman showed how to get a range with all the formulas. If
> you're doing something like changing the fill color, you can do it all at
> once--you don't need to loop through those cells.