How find all formulas on tab?

  • Thread starter Thread starter Ian Elliott
  • Start date Start date
I

Ian Elliott

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.
 
Hi Ian

MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Count

Or manual

F5
Special
Check formulas
OK
 
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.
 
Ian Elliott said:
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.
Click to expand...

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
 
Back
Top