Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How find all formulas on tab?
You could try searching for the "=" sign :o)
Hope this helps Julia |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I am trying to find cotangent in the formulas. | Excel Discussion (Misc queries) | |||
Find Cells with Formulas | Excel Discussion (Misc queries) | |||
Find Cells that do not have Formulas in them. | Excel Worksheet Functions | |||
where can I find help with excel formulas? | Excel Discussion (Misc queries) | |||
I NEED TO FIND TWO FORMULAS | Excel Worksheet Functions |