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

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

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