Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display Empty Cell When Using SUM formula
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display Empty Cell When Using SUM formula
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display Empty Cell When Using SUM formula
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display Empty Cell When Using SUM formula
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display Empty Cell When Using SUM formula
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display Empty Cell When Using SUM formula
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display Empty Cell When Using SUM formula
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display Empty Cell When Using SUM formula
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
format a cell with a formula so an empty reference cell shows blan | Excel Discussion (Misc queries) | |||
formula, move to previous cell when the current cell=0 or empty | Excel Discussion (Misc queries) | |||
How do I leave formula cell blank if 2nd reference cell is empty? | Excel Discussion (Misc queries) | |||
How do I set a cell to "Empty" so that it does not display in a ch | Charts and Charting in Excel | |||
Display a dialog box if cell is empty | Excel Discussion (Misc queries) |