summing by color

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

april - 06 Dec 2007 18:36 GMT

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

Signature

aprilshowers

Reply to this Message

FSt1 - 06 Dec 2007 19:01 GMT

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

Regards
FSt1

> 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

Reply to this Message

april - 06 Dec 2007 19:05 GMT

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

Signature

aprilshowers

> hi
> there are 10 different function on that page not counting "other function".
[quoted text clipped - 9 lines]
> >
> > thank you

Reply to this Message

Chip Pearson - 06 Dec 2007 19:16 GMT

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.

Signature

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

> sorry that i wasn't clear. here is the function
>
[quoted text clipped - 38 lines]
>> >
>> > thank you

Reply to this Message

FSt1 - 06 Dec 2007 19:21 GMT

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

> sorry that i wasn't clear. here is the function
>
[quoted text clipped - 35 lines]
> > >
> > > thank you

Reply to this Message

Gord Dibben - 06 Dec 2007 19:33 GMT

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

>hi
>I am guessing bob is right. i put the function in my personal.xls and try to
[quoted text clipped - 43 lines]
>> > >
>> > > thank you

Reply to this Message

Bob Phillips - 06 Dec 2007 19:09 GMT

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

Signature

HTH

Bob

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

>i have copied the code from cpearson.com/excel/colors.htm which allows you
>to
[quoted text clipped - 4 lines]
>
> thank you

Reply to this Message

april - 06 Dec 2007 19:21 GMT

it's a module in the same workbook

Signature

aprilshowers

> Different workbook? It expects it to be in the same workbook.
>
[quoted text clipped - 6 lines]
> >
> > thank you

Reply to this Message

SteveDB1 - 10 Dec 2007 17:44 GMT

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.

Reply to this Message

Gord Dibben - 10 Dec 2007 18:16 GMT

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

>April,
>with this function you need to use the following "preface" to access the
[quoted text clipped - 16 lines]
>>
>> thank you

Reply to this Message

SteveDB1 - 10 Dec 2007 19:18 GMT

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?

> Or put the functions in a new workbook and save as an Add-in.
>
[quoted text clipped - 25 lines]
> >>
> >> thank you

Reply to this Message

Gord Dibben - 10 Dec 2007 19:50 GMT

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

Gord

>Gord,
>So, if I get this correctly, you're saying that we can place the UDF's-- in
[quoted text clipped - 30 lines]
>> >>
>> >> thank you

Reply to this Message

SteveDB1 - 10 Dec 2007 20:01 GMT

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!

> Yes, you can access UDF's and macros directly through an Add-in....Excel 2007
> XLAM in your case.
[quoted text clipped - 35 lines]
> >> >>
> >> >> thank you

Reply to this Message