Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cell formula
I wanted to clear all the cells which have formula on it.
Are there any function to check this like "Isformula" in VBA? Any information is great appreciated, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cell formula
Try something like this...
Sheet1.Cells.SpecialCells(xlCellTypeFormulas).Clea rContents which clears all of the formulas on sheet 1 -- HTH... Jim Thomlinson "Souris" wrote: I wanted to clear all the cells which have formula on it. Are there any function to check this like "Isformula" in VBA? Any information is great appreciated, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cell formula
Thanks for the informaiton,
It works, but it fails if there is no formula on the spreadsheet. Should I check that does spreadsheet have formula before run the code? Thanks again, "Jim Thomlinson" wrote: Try something like this... Sheet1.Cells.SpecialCells(xlCellTypeFormulas).Clea rContents which clears all of the formulas on sheet 1 -- HTH... Jim Thomlinson "Souris" wrote: I wanted to clear all the cells which have formula on it. Are there any function to check this like "Isformula" in VBA? Any information is great appreciated, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cell formula
Hi Souris,
Whenever using the specialCells method it is advisable to use a precautionary error handler. Dim SH As Worksheet Dim rng As Range Set SH = Sheets("Sheet1") On Error Resume Next Set rng = SH.Cells.SpecialCells(xlFormulas) On Error GoTo 0 If Not rng Is Nothing Then rng.ClearContents --- Regards, Norman "Souris" wrote in message ... Thanks for the informaiton, It works, but it fails if there is no formula on the spreadsheet. Should I check that does spreadsheet have formula before run the code? Thanks again, "Jim Thomlinson" wrote: Try something like this... Sheet1.Cells.SpecialCells(xlCellTypeFormulas).Clea rContents which clears all of the formulas on sheet 1 -- HTH... Jim Thomlinson "Souris" wrote: I wanted to clear all the cells which have formula on it. Are there any function to check this like "Isformula" in VBA? Any information is great appreciated, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cell formula
Is it possible to clear all the format like color and font...etc
Thanks millions, "Norman Jones" wrote: Hi Souris, Whenever using the specialCells method it is advisable to use a precautionary error handler. Dim SH As Worksheet Dim rng As Range Set SH = Sheets("Sheet1") On Error Resume Next Set rng = SH.Cells.SpecialCells(xlFormulas) On Error GoTo 0 If Not rng Is Nothing Then rng.ClearContents --- Regards, Norman "Souris" wrote in message ... Thanks for the informaiton, It works, but it fails if there is no formula on the spreadsheet. Should I check that does spreadsheet have formula before run the code? Thanks again, "Jim Thomlinson" wrote: Try something like this... Sheet1.Cells.SpecialCells(xlCellTypeFormulas).Clea rContents which clears all of the formulas on sheet 1 -- HTH... Jim Thomlinson "Souris" wrote: I wanted to clear all the cells which have formula on it. Are there any function to check this like "Isformula" in VBA? Any information is great appreciated, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cell formula
Hi Souris,
Is it possible to clear all the format like color and font...etc Dim SH As Worksheet Dim rng As Range Set SH = Sheets("Sheet1") On Error Resume Next Set rng = SH.Cells.SpecialCells(xlFormulas) On Error GoTo 0 If Not rng Is Nothing Then rng.Clear --- Regards, Norman "Souris" wrote in message ... Is it possible to clear all the format like color and font...etc Thanks millions, "Norman Jones" wrote: Hi Souris, Whenever using the specialCells method it is advisable to use a precautionary error handler. Dim SH As Worksheet Dim rng As Range Set SH = Sheets("Sheet1") On Error Resume Next Set rng = SH.Cells.SpecialCells(xlFormulas) On Error GoTo 0 If Not rng Is Nothing Then rng.ClearContents --- Regards, Norman "Souris" wrote in message ... Thanks for the informaiton, It works, but it fails if there is no formula on the spreadsheet. Should I check that does spreadsheet have formula before run the code? Thanks again, "Jim Thomlinson" wrote: Try something like this... Sheet1.Cells.SpecialCells(xlCellTypeFormulas).Clea rContents which clears all of the formulas on sheet 1 -- HTH... Jim Thomlinson "Souris" wrote: I wanted to clear all the cells which have formula on it. Are there any function to check this like "Isformula" in VBA? Any information is great appreciated, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check if Conditional Format is True or False / Check cell Color | Excel Worksheet Functions | |||
formula to check Cell value within specific character | Excel Discussion (Misc queries) | |||
How do I check for an empty cell in a formula? | Excel Discussion (Misc queries) | |||
Check if a cell has a formula. | Excel Worksheet Functions | |||
How to check whether a cell contains a formula or a value | Excel Worksheet Functions |