VBA Help - 2 decimal places

[Alex501]

Hi

Can someone help me with some VBA code, I have the following to sum cells based on background colour;

1. Function SumByColor(CellColor As Range, rRange As Range)
2. Dim cSum As Long
3. Dim ColIndex As Integer
4. ColIndex = CellColor.Interior.ColorIndex
5. For Each cl In rRange
6. If cl.Interior.ColorIndex = ColIndex Then
7. cSum = WorksheetFunction.SUM(cl, cSum)
8. End If
9. Next cl
10. SumByColor = cSum
11. End Function

The above only sums whole numbers, how do I amend it so that it will sum to 'X' amount of decimal places. I know I need to add ROUND into it but my attempts have failed.

Thanks

Alex

 

[dmt32]

Hi,
cSum is Declared as a Long Integer - Try changing it to Double and see if this does what you want

Function SumByColor(CellColor As Range, rRange As Range) As Double
    Dim cSum As Double
    Dim ColIndex As Integer
    ColIndex = CellColor.Interior.ColorIndex
    For Each cl In rRange
    If cl.Interior.ColorIndex = ColIndex Then
    cSum = WorksheetFunction.Sum(cl, cSum)
    End If
    Next cl
    SumByColor = cSum
End Function

Also, maybe a good idea for the Function to return the same

Dave

 

[ZVI]

Hi,
Try this:

Function SumByColor(CellColor As Range, rRange As Range, Optional num_digits = 2)
  Dim cl As Range
  Dim cSum As Double
  Dim ColIndex As Integer
  ColIndex = CellColor.Interior.ColorIndex
  For Each cl In rRange
    If cl.Interior.ColorIndex = ColIndex Then
      cSum = cSum + cl.Value
    End If
  Next cl
  SumByColor = WorksheetFunction.Round(cSum, num_digits)
End Function

The optional 3d parameter is the number of digits (decimal places) for rounding, if omitted then it is equal to 2 digits

 

[Alex501]

Hi,
Try this:

Function SumByColor(CellColor As Range, rRange As Range, Optional num_digits = 2)
  Dim cl As Range
  Dim cSum As Double
  Dim ColIndex As Integer
  ColIndex = CellColor.Interior.ColorIndex
  For Each cl In rRange
    If cl.Interior.ColorIndex = ColIndex Then
      cSum = cSum + cl.Value
    End If
  Next cl
  SumByColor = WorksheetFunction.Round(cSum, num_digits)
End Function

The optional 3d parameter is the number of digits (decimal places) for rounding, if omitted then it is equal to 2 digits

Works like a charm, thank you.

 

[ZVI]

You are welcome, glad it helped.