summing by color

[april]

i have copied the code from cpearson.com/excel/colors.htm which allows you to
sum a range of cells by color. this worked fine in one spreadsheet but when
i copied the same code to another spreadsheet i get a NAME? error. any
suggestions?

thank you

 

[FSt1]

hi
there are 10 different function on that page not counting "other function".
please post the one you are using.

Regards
FSt1

 

[april]

sorry that i wasn't clear. here is the function

Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng

End Function

 

[Bob Phillips]

Different workbook? It expects it to be in the same workbook.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Chip Pearson]

Make sure you put the code in a code module (Insert menu, Module), NOT the
ThisWorkbook module and not one of the Sheet modules. Also, the code must be
in the same workbook as the cell that calls it.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[april]

it's a module in the same workbook

 

[FSt1]

hi
I am guessing bob is right. i put the function in my personal.xls and try to
sum some color in a blank workbook. got the name error. but when i put the
function in the blank workbook, it worked. countbycolor too.
first time i have work with that and bob is way more experienced than me.
regards
FSt1

 

[Gord Dibben]

FSt1

If you preface the function name with Personal.xls you will not get the error.

=Personal.xls!SumByColor(A1:A10)


Gord Dibben MS Excel MVP

 

[SteveDB1]

April,
with this function you need to use the following "preface" to access the
macro.
in your worksheet cell write hte following:
=worksheetname!sumbycolor(range,colornumber,...)
Part of hte problem you've encountered is that you're accessing a function
that's stored in another workbook, and it does not call to that workbook
UNLESS you state it.
I place all of my macros in a single workbook-- personal.xlsb, so when I
call to that function I write hte following:
=personal.xlsb!SumByColor(.......)
It'll work if you choose that.
Best.

 

[Gord Dibben]

Or put the functions in a new workbook and save as an Add-in.

Load through Tools>Add-ins and you won't have to preface the UDF with the
filename.

=SumByColor(A1:A10) will suffice.


Gord Dibben MS Excel MVP

 

[SteveDB1]

Gord,
So, if I get this correctly, you're saying that we can place the UDF's-- in
general-- in an XLAM workbook, and then add them to our add-in lists, and
access them through that?

 

[Gord Dibben]

Yes, you can access UDF's and macros directly through an Add-in....Excel 2007
XLAM in your case.


Gord

 

[SteveDB1]

gads.... you gotta love it.
It works!
And, I was even able to set up my own "autofill" macro for my ribbon, and
that works too....
Thank you bery, bery, bery much!