Mat2020
11-16-2017, 09:00 AM
Hi all,
I have a code that sums the values of cells with the same colour in a range of excel cells, and it works nicely... well, almost.
It works nicely if there are only numbers in the range of cells. If I add text in any of them, I get #VALUE!
How do I change the code so it ignores the text and only sums the numbers?
Code below:
Option Explicit
Function ColorMath(InputRange As Range, ReferenceCell As Range, Optional Action As String = "S")
Application.Volatile
' Action can be S to SUM, A to AVERAGE, or C to COUNT
' If not specified the default Action is SUM
Dim ReferenceColor As Long
Dim CellCount As Long
Dim Result As Variant
Dim Cell As Range
Action = UCase(Action)
Result = 0
CellCount = 0
ReferenceColor = ReferenceCell.Interior.Color
If Action = "S" Or Action = "A" Then
For Each Cell In InputRange
If Cell.Interior.Color = ReferenceColor Then
Result = Result + Cell.Value
CellCount = CellCount + 1
End If
Next Cell
End If
If Action = "C" Then
For Each Cell In InputRange
If Cell.Interior.Color = ReferenceColor Then Result = Result + 1
Next Cell
End If
If Action = "A" Then
Result = Result / CellCount
End If
ColorMath = Result
End Function
I have a code that sums the values of cells with the same colour in a range of excel cells, and it works nicely... well, almost.
It works nicely if there are only numbers in the range of cells. If I add text in any of them, I get #VALUE!
How do I change the code so it ignores the text and only sums the numbers?
Code below:
Option Explicit
Function ColorMath(InputRange As Range, ReferenceCell As Range, Optional Action As String = "S")
Application.Volatile
' Action can be S to SUM, A to AVERAGE, or C to COUNT
' If not specified the default Action is SUM
Dim ReferenceColor As Long
Dim CellCount As Long
Dim Result As Variant
Dim Cell As Range
Action = UCase(Action)
Result = 0
CellCount = 0
ReferenceColor = ReferenceCell.Interior.Color
If Action = "S" Or Action = "A" Then
For Each Cell In InputRange
If Cell.Interior.Color = ReferenceColor Then
Result = Result + Cell.Value
CellCount = CellCount + 1
End If
Next Cell
End If
If Action = "C" Then
For Each Cell In InputRange
If Cell.Interior.Color = ReferenceColor Then Result = Result + 1
Next Cell
End If
If Action = "A" Then
Result = Result / CellCount
End If
ColorMath = Result
End Function