Display Empty Cell When Using SUM formula

  • Thread starter Thread starter Koomba
  • Start date Start date
K

Koomba

I am using a Custom Sales Order Template. I use a macro to clear contents
(from previous sales order). I use SUM formula in the TOTAL cell and wonder
if I can have the TOTAL cell show as empty (but leave formula intact) after
macro clears contents. I am being picky here, I know.

Here is Macro:

Sub NewSalesOrder()
'
' NewSalesOrder Macro
' Clears Sales Order for new data entry
'
' Keyboard Shortcut: Ctrl+b
'
Range("B2").Select
Selection.ClearContents
Range("B4:B13").Select
Selection.ClearContents
Range("E14").Select
Selection.ClearContents
End Sub
 
Is the Total cell one of the cells cleared in your macro?

If so, why clear (delete) the formula? Just write the formula like this:

=IF(SUM(.....)=0,"",SUM(.....))

And remove that cell reference from your macro.

Also, you can simplify the macro to:

Sub NewSalesOrder()
Range("B2,B4:B13,E14").ClearContents
End Sub
 
Hi,

You can use the formula:

IF(SUM(B4:B13),SUM(B4:B13),"")

or you can leave the original formula and choose Tools, Options, View tab,
and uncheck Zero values.

or you can applly a custom format like #,##0_);(#,##0);; to the cells.
 
Assuming that your Total is in F14, I simply your macro and add a total
formula
in F14. Adjust to suit yours.

Sub Clear()
'
Range("B2,B4:B13,E14").ClearContents
Range("F14").Formula = "=IF(SUM(B4:B13)=0,"""",SUM(B4:B13))"

End Sub

Regards,
 
Before Harlan jumps in and being more picky than even the OP, would it not
be better to use:

=IF(COUNT(.....)=0,"",SUM(.....))

I know that it is a Sales Order Template but I suppose that it is always
possible that if there was a cancelled order, the credit note is entered as
a negative.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
I don't know....six of one, half dozen of the other!

SUM saves you 2 keystrokes and you could eliminate the =0 to save 2 more!
 
To all who have sent solutions, thank you. Your suggestions are helping me in
a very big way. I will always try and find my solutions before imposing on
you all.
 
Back
Top