Function returns #Name?

[jeremiah]

I am using a SumColor function and is returning the #Name? error. Haven't
used many functions so don't quite understand what the issue is.

code is below along with =SumColor (A10, D1D60)

Function SumColor(rColor As Range, rSumRange As Range)

Dim rCell As Range
Dim iCol As Integer
Dim vResult
iCol = rColor.Interior.ColorIndex

For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = WorksheetFunction.SUM(rCell) + vResult
End If
Next rCell

SumColor = vResult
End Function

Thanks again for any help.

 

[Rick Rothstein]

Without looking at your code, the formula you are putting in your cell is
not correct... there should not be a space between the function name and the
opening parenthesis and your second argument is missing the colon separator
between the two cell addresses. It should be...

=SumColor(A10, D160)

 

[ryguy7272]

Sum colored cells w/out VBA:
Find all colored cells and sum all:
Here is a quick way without VBA or formulas.
Edit > Find > clear "Find What" > Options > Format >
"Choose Format From Cell" > select sample cell >
Find All > scroll to end of list, press CTRL+SHIFT
and click on the last entry of the list.
Right click Status Bar and check Sum.

These functions should work for you too:
Function SumColors(rSumRng As Range, ParamArray aColorIndex() As Variant) As
Double
Dim cell As Range
Dim i As Long
Dim dTempSum As Double

For Each cell In rSumRng.Cells
For i = LBound(aColorIndex) To UBound(aColorIndex)
If cell.Interior.ColorIndex = aColorIndex(i) Then
dTempSum = Application.Sum(dTempSum, cell.Value)
End If
Next i
Next cell

SumColors = dTempSum
End Function

Function SumColors2(rSumRng As Range, ParamArray aColorIndex() As Variant)
As Double
Dim cell As Range
Dim i As Long
Dim dTempSum As Double
Dim lColorString As Long

For Each cell In rSumRng.Cells
For i = LBound(aColorIndex) To UBound(aColorIndex)
Select Case TypeName(aColorIndex(i))
Case "Double"
If cell.Interior.ColorIndex = aColorIndex(i) Then
dTempSum = Application.Sum(dTempSum, cell.Value)
End If
Case "Range"
If cell.Interior.Color = aColorIndex(i).Interior.Color
Then
dTempSum = Application.Sum(dTempSum, cell.Value)
End If
Case "String"
Select Case UCase(aColorIndex(i))
Case "BLACK"
lColorString = vbBlack
Case "BLUE"
lColorString = vbBlue
Case "CYAN"
lColorString = vbCyan
Case "GREEN"
lColorString = vbGreen
Case "MAGENTA"
lColorString = vbMagenta
Case "RED"
lColorString = vbRed
Case "WHITE"
lColorString = vbWhite
Case "YELLOW"
lColorString = vbYellow
End Select ' & nbsp
If cell.Interior.Color = lColorString Then
dTempSum = Application.Sum(dTempSum, cell.Value)
End If
End Select
Next i
Next cell

SumColors2 = dTempSum
End Function


Regards,
Ryan---

 

[Chip Pearson]

A #NAME error occurs when Excel cannot find a named range (which is
not the case here) or when it cannot find a function (which is likely
the case here). The SumColor VBA code must be placed in a standard
code module. Press ALT F11 to open the VBA Editor, then go to the
Insert menu and choose Module. This will create a module named Module1
in your workbook's code project. Paste the SumColor function in that
module. Then close VBA and return to Excel. Select the cell that calls
the SumColor function, press F2 and then ENTER.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[jeremiah]

Thank you, I was trying to paste it to my personal.xls

 

[Gord Dibben]

You can paste it into a module in your Personal.xls........good place to
have so's it is available for all open workbooks.

What you then have to do is add the workbook reference to the function.

=Personal.xls!SumColor (A10, D1D60)


Gord Dibben MS Excel MVP