Incorporate this into your code or just run it in tandem with your other code.

Sub NATrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISNA*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISNA(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Fri, 25 Jan 2008 14:48:01 -0800, pm wrote:

Hey Ron,
That worked...now I'm getting an #N/A in my formula fields. So, I need an
IF statement to blank the error???

"Ron Rosenfeld" wrote:

On Thu, 24 Jan 2008 08:17:08 -0800, pm wrote:

I have a macro that clears the contents of a range of cells; however, I would
like to keep the formulas in certain cells, just clear the data....how can I
do this? Thanks.


'Clear User Lists on Control Sheet
Sheets("Control").Select
Range("A8:E65536").Select
Selection.ClearContents
Range("A5").Select
Sheets("Control").Cells(5, 1) = UCase(Sheets("Control").Cells(5, 1))


Worksheets("Control").Range("A8:E65536").SpecialCe lls(xlCellTypeConstants).ClearContents


--ron