Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change a number to text
Hi Lori,
Al's suggestion should work to exlude the number from being included in the SUM. Alternatively, the user could use a UDF instead of SUM: Public Function SumIfNotBold(rng As Range) As Variant Dim c As Range Dim v As Variant Application.Volatile For Each c In rng If Not c.Font.Bold And IsNumeric(c.Value) Then v = v + c.Value Next c SumIfNotBold = v End Function The SUM forumula can then be changed to =SumIfNotBold(<your range). That way, if the user makes a number bold, it will not be included in the total. This will update on each calculation of the worksheet. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Lori F. wrote: Help, please? I have a user who has a column of accts payable numbers and a total at the bottom (=Sum). When a payment is made, she wants to be able to bold a number and tell Excel not to include it in the total at the bottom. I've created a macro to bold the current cell and format it as text instead of a number. The cell that is now text should not be calculated the function, but it is. What am I doing wrong? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change a number to text
Jake,
Another great suggestion - thanks! Lori -----Original Message----- Hi Lori, Al's suggestion should work to exlude the number from being included in the SUM. Alternatively, the user could use a UDF instead of SUM: Public Function SumIfNotBold(rng As Range) As Variant Dim c As Range Dim v As Variant Application.Volatile For Each c In rng If Not c.Font.Bold And IsNumeric(c.Value) Then v = v + c.Value Next c SumIfNotBold = v End Function The SUM forumula can then be changed to =SumIfNotBold (<your range). That way, if the user makes a number bold, it will not be included in the total. This will update on each calculation of the worksheet. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Lori F. wrote: Help, please? I have a user who has a column of accts payable numbers and a total at the bottom (=Sum). When a payment is made, she wants to be able to bold a number and tell Excel not to include it in the total at the bottom. I've created a macro to bold the current cell and format it as text instead of a number. The cell that is now text should not be calculated the function, but it is. What am I doing wrong? Thanks! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change text into number? | Excel Discussion (Misc queries) | |||
Formula to change scientific number to regular number or text | Excel Discussion (Misc queries) | |||
Change number format from text to number? | New Users to Excel | |||
Change Text to a number | Excel Worksheet Functions | |||
EZ Q 4 U: How do I change a number to text, based on the number | Excel Worksheet Functions |