CONVERT FORMULA INTO VALUE EXCEPT SUBTOTAL FORMULA

[shaqil]

Dear All,

Normally I have with many formula but when I forward the file i
convert all formulas with their values (with the help of Paste
Special) but I want to replace all formulas with values except
subtotal & sum formulas.

Can anyone help me.

Thnx in advance.

 

[Norman Jones]

Hi Shaqil,

Try something like:

'============>>
Public Sub Tester001()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim rCell As Range

Set WB = Workbooks("MyBook1.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

On Error Resume Next
Set Rng = SH.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If Not Rng Is Nothing Then
For Each rCell In Rng.Cells
With rCell
If InStr(1, .Formula, "Sum", vbTextCompare) = 0 _
And InStr(1, .Formula, "SubTotal", _
vbTextCompare) = 0 Then
.Value = .Value
End If
End With
Next rCell
End If
End Sub
'<<============