Check cell formula

[Souris]

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,

[Jim Thomlinson]

Try something like this...

Sheet1.Cells.SpecialCells(xlCellTypeFormulas).ClearContents

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,
>
>
>

[Souris]

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).ClearContents
>
> 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,
> >
> >
> >

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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).ClearContents
>>
>> 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,
>> >
>> >
>> >

[Souris]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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).ClearContents
> >>
> >> 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,
> >> >
> >> >
> >> >
>
>
>

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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).ClearContents
>> >>
>> >> 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,
>> >> >
>> >> >
>> >> >
>>
>>
>>