How find all formulas on tab?
 

Thanks in advance.
I have a request from my supervisor to make an Excel file
value only. I can do that by copying and then paste
special, values. But before I do that, I want to check if
there are any cells in the tab that have formulas, and
therefore "need" to be pasted special. Is there a way to
get info on which cells are formulas? I know of the Trace
Precedents and Trace Dependents buttons, but I don't know
how to do for a whole worksheet at a time.
Thanks again.

How find all formulas on tab?
 

You could try searching for the "=" sign :o)

Hope this helps

Julia

How find all formulas on tab?
 

Hi Ian

MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) .Count

Or manual

F5
Special
Check formulas
OK

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ian Elliott" wrote in message ...
Thanks in advance.
I have a request from my supervisor to make an Excel file
value only. I can do that by copying and then paste
special, values. But before I do that, I want to check if
there are any cells in the tab that have formulas, and
therefore "need" to be pasted special. Is there a way to
get info on which cells are formulas? I know of the Trace
Precedents and Trace Dependents buttons, but I don't know
how to do for a whole worksheet at a time.
Thanks again.

How find all formulas on tab?
 

Dim rng as Range
On Error Resume Next
set rng = cells.SpecialCells(xlFormulas)
On Error goto 0
if not rng is nothing then
rng.Formula = rng.Value
End if

Should change to values where appropriate.

--
Regards,
Tom Ogilvy


"Ian Elliott" wrote in message
...
Thanks in advance.
I have a request from my supervisor to make an Excel file
value only. I can do that by copying and then paste
special, values. But before I do that, I want to check if
there are any cells in the tab that have formulas, and
therefore "need" to be pasted special. Is there a way to
get info on which cells are formulas? I know of the Trace
Precedents and Trace Dependents buttons, but I don't know
how to do for a whole worksheet at a time.
Thanks again.

How find all formulas on tab?
 

"Ian Elliott" wrote in message ...
Thanks in advance.
I have a request from my supervisor to make an Excel file
value only. I can do that by copying and then paste
special, values. But before I do that, I want to check if
there are any cells in the tab that have formulas, and
therefore "need" to be pasted special. Is there a way to
get info on which cells are formulas? I know of the Trace
Precedents and Trace Dependents buttons, but I don't know
how to do for a whole worksheet at a time.
Thanks again.

Ian:
There is a few ways to see which cells contain formulas:

Option 1
Edit menu - Goto - click the "Special" button - check the Formulas box
This will highlight all cells on a sheet that contain formulas.

Option 2
Select Tools - Options - View tab - Window Options - check the
Formulas box.
This will then show the actual formula in each cell on the sheet.
Note that it will change your view of the sheet as the cells then
expand to show the entire formula on the screen. You can change back
to regular view by unchecking the Formulas box.

Option 1 may serve better, as you only want to see if formulas exist
on the sheet prompting you to use Paste Special-Values. If no formulas
exist on the sheet, you'll receive a message that says "No Cells Were
Found" meaning the sheet is already all values and normal Paste will
work fine.

Hope it helps,

Rick