How can I loop through all Formula in a workbook?
 

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

Anyone have this code snippet?

Thanks,

How can I loop through all Formula in a workbook?
 

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" wrote in message
...
I tried Cells.SpecialCells(.... xlCellTypeFormulas...) but no luck.

Anyone have this code snippet?

Thanks,

How can I loop through all Formula in a workbook?
 

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" wrote in message
...
I tried Cells.SpecialCells(.... xlCellTypeFormulas...) but no luck.

Anyone have this code snippet?

Thanks,

How can I loop through all Formula in a workbook?
 

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" wrote in message
...
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

How can I loop through all Formula in a workbook?
 

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" wrote in message
...
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

How can I loop through all Formula in a workbook?
 

Hi Dave,

I'd add one more line:

So would I!

Thank you!


---
Regards,
Norman



"Dave Peterson" wrote in message
...
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.