How do I find all cells with formulas

[Guest]

How do I find all cells with formulas, Excel 2003. Thanks in advance.

 

[Don Guillett]

You might try looking in the vba help index for
HASFORMULA
and build a looping macro
for each c in range("yours")
if c.hasformula
next

 

[Dave Peterson]

One way...
Select the range to search (all the cells on the worksheet???)
Edit|goto|special|check Formulas

And the selection will change to just those cells with formulas.

 

[Gord Dibben]

One method.

View>View Formulas and visually look for them.

Another method using a macro which colors all cells with formulas in all
worksheets.

Sub Formulas()
Dim rng As Range
Dim fcell As Range
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
Set rng = wks.UsedRange
For Each fcell In rng
If fcell.HasFormula Then
fcell.Interior.ColorIndex = 6 'yellow
End If
Next fcell
Next wks
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Duh!!

Way too easy<g>

Gord

One way...
Select the range to search (all the cells on the worksheet???)
Edit|goto|special|check Formulas

And the selection will change to just those cells with formulas.

 

[Dave Peterson]

Depends on what you want to do with the cells after you find them.

There's always lots of options!

 

[Gord Dibben]

I never even thought of the Goto>Special.

Why does Excel hide all those neat tricks from guys like me with short memories?

 

[Dave Peterson]

I once suggested using shortcut keys for something.

Myrna Larson wrote a followup that going through the menus is a good way to see
what MS hid under the menus.

It makes sense to me still.

(I can't wait for xl2007 <bg>.)