Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format Function Output to numeric (#,###.00)
The following UDF works great except it rounds to $1,234.00 versus $1,234.56.
How can I get the pennies to show? Function SumByColor(CellColor As Integer, SumRange As Range) As Long Dim MyCell As Range Dim myTotal As Double For Each MyCell In SumRange If MyCell.Interior.ColorIndex = CellColor Then myTotal = WorksheetFunction.Sum(MyCell) + myTotal End If Next MyCell SumByColor = myTotal End Function |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format Function Output to numeric (#,###.00)
Change the Function's return datatype to Double from Long. Long is an
integer datatype, which is truncating the summed values. Actually in your example it should round up to 1235, but I assume you concocted that example and that's not a real one. "Jim May" wrote in message ... The following UDF works great except it rounds to $1,234.00 versus $1,234.56. How can I get the pennies to show? Function SumByColor(CellColor As Integer, SumRange As Range) As Long Dim MyCell As Range Dim myTotal As Double For Each MyCell In SumRange If MyCell.Interior.ColorIndex = CellColor Then myTotal = WorksheetFunction.Sum(MyCell) + myTotal End If Next MyCell SumByColor = myTotal End Function |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format Function Output to numeric (#,###.00)
Never mind - Not until after I posted the query did I see that it is Originally
designed to out put as LONG versus Double << Changed and everythings OK Sorry,, "Jim May" wrote: The following UDF works great except it rounds to $1,234.00 versus $1,234.56. How can I get the pennies to show? Function SumByColor(CellColor As Integer, SumRange As Range) As Long Dim MyCell As Range Dim myTotal As Double For Each MyCell In SumRange If MyCell.Interior.ColorIndex = CellColor Then myTotal = WorksheetFunction.Sum(MyCell) + myTotal End If Next MyCell SumByColor = myTotal End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA - Function Output | Excel Discussion (Misc queries) | |||
Combo Box - format output as time | Excel Discussion (Misc queries) | |||
Change number (in text format) to numeric format | Excel Discussion (Misc queries) | |||
format cell from data input to output form | Excel Worksheet Functions | |||
HOW CAN I SHOW OUTPUT IN FEET INCH FORMAT | New Users to Excel |