Normally, you would hand that in the formula itself by checking for the
error with an IF function call and returning "" if there is an error
otherwise return your formula. See Gord Dibben's post (located under Ron
Rosenfeld message) for an addition to the macro that will handle modifying
your formula for you (but keep in mind the idea for future formulas you
write).

Rick


"pm" wrote in message
...
Thanks Rick,

How do I get rid of the error N/A in the formula fields,
please.....Thanks.

"Rick Rothstein (MVP - VB)" wrote:

Sorry, I missed the "formula" part of your question. This should work...

With Sheets("Resource Details")
.Range("A8:E65536").SpecialCells(xlCellTypeConstan ts).ClearContents
.Cells(5, 1) = UCase(.Cells(5, 1))
End With

Rick


"pm" wrote in message
...
Hi Rick,

I tried running this macro with the empty string "", but it still
erased
my
formula..any other suggestions?
Thanks in advance.

"Rick Rothstein (MVP - VB)" wrote:

Instead of ClearContents, you can just assign the empty string "" to
the
range. Here is your code modified to do this and with all those
unnecessary
Select/Selections removed (and a With/EndWith block to make it more
readable)...

With Sheets("Resource Details")
.Range("A8:E65536") = ""
.Cells(5, 1) = UCase(.Cells(5, 1))
End With

Rick


"pm" wrote in message
...
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))