Vba that deletes formula results( resolved thx)

[James]

Hi Group,

I'm trying come up some code that on commandbutton_click will read sheet1 & sheet2 and clear the cells that have the result of a formula, not any text or formulas.

Any help would be appreciated

Jim

_________________


[ This Message was edited by: James on 2002-05-20 19:38 ]

[s-o-s]

On 2002-05-20 16:17, James wrote:
Hi Group,

I'm trying come up some code that on commandbutton_click will read sheet1 & sheet2 and clear the cells that have the result of a formula, not any text or formulas.

Any help would be appreciated

Jim

Jim,

A very quick way of doing this would be to copy the whole sheet and paste values

Sub turnoff()
     Sheets("Sheet1").select
     Cells.Copy
     Cells.PasteSpecial xlPasteValues
     Sheets("Sheet2").select
     Cells.Copy
     Cells.PasteSpecial xlPasteValues
End Sub

Hope this helps

[NateO]

James, by

clear the cells that have the result of a formula, not any text or formulas.

Do you mean the formula's precedents? E.g., =a1+a2, keep this formula but clear a1 & a2?

Do you have a clean range of precedants? Am I remotely following your post?

[James]

Hi Nate,

Yes, that's what i'm trying to accomplish.

Regards,
James

[Ivan F Moala]

On 2002-05-20 17:51, James wrote:
Hi Nate,

Yes, that's what i'm trying to accomplish.

Regards,
James

Hi James the following just clears all your
constants in the sheets ?? will this do it

Sub FrmClr_precedents()

'// Just clears Constants

' const = Numbers,

On Error Resume Next

Sheets("Sheet1").Cells.SpecialCells(2, 21).ClearContents

Sheets("Sheet2").Cells.SpecialCells(2, 21).ClearContents

On Error GoTo 0

End Sub

[Tom Schreiner]

Hi
If Ivans code works, then use it.
However, Ivan's code will clear cells which are not Precendents as well.

This will only clear Precedents:

Sub ClearNonFormulaPrecedents()

Dim c As Range, PrecC As Range, Pc As Range

On Error Resume Next

For Each c In ActiveSheet.UsedRange

    Set PrecC = c.DirectPrecedents

    If Err.Number = 1004 Then

        Err.Clear

        GoTo NoCells

    End If

    For Each Pc In PrecC

        If Not Pc.Formula Then Pc.ClearContents

    Next

NoCells:

Next

Set c = Nothing: Pc = Nothing

End Sub

Tom

[NateO]

Numeric constants. Good one Ivan!

I had been playing with:

Sub ejrignv()

Dim cell As Range, cl As Range, ws As Worksheet

For Each ws In ThisWorkbook.Worksheets(Array("sheet1", "sheet2"))

    On Error GoTo 1

    For Each cell In ws.[a1].SpecialCells(xlCellTypeFormulas)

        On Error Resume Next

        For Each cl In cell.Precedents

            If cl Is Nothing Then GoTo 2

            If cl.HasFormula = False Then cl.ClearContents

        Next cl

2:   Next cell

1: Next ws

End Sub

But what I found and did not like is that it won't clear precedents from other sheets. May have to do some more homework....Have a good one.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-20 19:03 ]

[NateO]

Tom's in on it too. A bunch of ideas, hope they are of help James.

[Ivan F Moala]

On 2002-05-20 19:02, NateO wrote:
Numeric constants. Good one Ivan!

I had been playing with:

Sub ejrignv()

Dim cell As Range, cl As Range, ws As Worksheet

For Each ws In ThisWorkbook.Worksheets(Array("sheet1", "sheet2"))

    On Error GoTo 1

    For Each cell In ws.[a1].SpecialCells(xlCellTypeFormulas)

        On Error Resume Next

        For Each cl In cell.Precedents

            If cl Is Nothing Then GoTo 2

            If cl.HasFormula = False Then cl.ClearContents

        Next cl

2:   Next cell

1: Next ws

End Sub

But what I found and did not like is that it won't clear precedents from other sheets. May have to do some more homework....Have a good one.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-20 19:03 ]

Yeah that seemed to be the easiest way
I looked @ the precedents method BUT like you
said, it doesn't seem to support formulas
from Diff sheets...work around ?? look further......cheers Nate & Tom

[James]

Hi group,

Thanks for the replys. I jumped at Ivans, and it works. Although i will try them all.

Regards,
James