Forum Discussion
Laura_Simpson
Mar 15, 2024Copper Contributor
SumColor Function Rounding Up and Down Totals
Hello,
I am using the SumColor function with the following code. It is rounding the sums up or down and leaving odd pence across my cash flow. Can anyone tell me a way to stop this happening? I popped a screen shot example below too. Thank you. Laura
Function SumColor(SumRange As Range, ColorCode As Range)
Dim ColorCodeValue As Integer
Dim TotalSum As Long
ColorCodeValue = ColorCode.Interior.ColorIndex
Set rCell = SumRange
For Each rCell In SumRange
If rCell.Interior.ColorIndex = ColorCodeValue Then
TotalSum = TotalSum + rCell.Value
End If
Next rCell
SumColor = TotalSum
End Function
Laura_Simpson The described issue is caused by the data type used... Long (Long Integer) does not contain decimals. Try using type Double or Currency, depending on your needs (ie: Dim TotalSum As Double).
For more information on data types, see: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
- djclementsBronze Contributor
Laura_Simpson The described issue is caused by the data type used... Long (Long Integer) does not contain decimals. Try using type Double or Currency, depending on your needs (ie: Dim TotalSum As Double).
For more information on data types, see: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
- Laura_SimpsonCopper ContributorThis has worked! Thank you so much for your help. I really appreciate it.