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

--
Thanks from Salt Spring

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

--
Biff
Microsoft Excel MVP


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

--
Thanks from Salt Spring

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.


--
Thanks,
Shane Devenshire


"Koomba" wrote:

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

--
Thanks from Salt Spring

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,



"Koomba" wrote:

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

--
Thanks from Salt Spring

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


Replace @mailinator.com with @tiscali.co.uk


"T. Valko" wrote in message
...
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

--
Biff
Microsoft Excel MVP


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

--
Thanks from Salt Spring

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!

--
Biff
Microsoft Excel MVP


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"T. Valko" wrote in message
...
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

--
Biff
Microsoft Excel MVP


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

--
Thanks from Salt Spring

Thanks for taking me a step further
--
Thanks from Salt Spring


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


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

--
Thanks from Salt Spring

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.
--
Thanks from Salt Spring


"franciz" wrote:


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,



"Koomba" wrote:

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

--
Thanks from Salt Spring

You're welcome!

--
Biff
Microsoft Excel MVP


"Koomba" wrote in message
...
Thanks for taking me a step further
--
Thanks from Salt Spring


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


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

--
Thanks from Salt Spring