summing by color
 

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
--
aprilshowers

summing by color
 

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

Regards
FSt1

"april" wrote:

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
--
aprilshowers

summing by color
 

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
--
aprilshowers


"FSt1" wrote:

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

Regards
FSt1

"april" wrote:

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
--
aprilshowers

summing by color
 

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)

"april" wrote in message
...
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
--
aprilshowers

summing by color
 

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" wrote in message
...
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
--
aprilshowers


"FSt1" wrote:

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

Regards
FSt1

"april" wrote:

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
--
aprilshowers

summing by color
 

it's a module in the same workbook
--
aprilshowers


"Bob Phillips" wrote:

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)

"april" wrote in message
...
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
--
aprilshowers

summing by color
 

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

"april" wrote:

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
--
aprilshowers


"FSt1" wrote:

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

Regards
FSt1

"april" wrote:

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
--
aprilshowers

summing by color
 

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

On Thu, 6 Dec 2007 11:21:07 -0800, FSt1 wrote:

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

"april" wrote:

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
--
aprilshowers


"FSt1" wrote:

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

Regards
FSt1

"april" wrote:

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
--
aprilshowers

summing by color
 

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.

"april" wrote:

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
--
aprilshowers

summing by color
 

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

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

=SumByColor(A1:A10) will suffice.


Gord Dibben MS Excel MVP


On Mon, 10 Dec 2007 09:44:00 -0800, SteveDB1
wrote:

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.

"april" wrote:

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
--
aprilshowers

summing by color
 

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" wrote:

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

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

=SumByColor(A1:A10) will suffice.


Gord Dibben MS Excel MVP


On Mon, 10 Dec 2007 09:44:00 -0800, SteveDB1
wrote:

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.

"april" wrote:

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
--
aprilshowers

summing by color
 

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


Gord

On Mon, 10 Dec 2007 11:18:01 -0800, SteveDB1
wrote:

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" wrote:

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

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

=SumByColor(A1:A10) will suffice.


Gord Dibben MS Excel MVP


On Mon, 10 Dec 2007 09:44:00 -0800, SteveDB1
wrote:

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.

"april" wrote:

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
--
aprilshowers

summing by color
 

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!


"Gord Dibben" wrote:

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


Gord

On Mon, 10 Dec 2007 11:18:01 -0800, SteveDB1
wrote:

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" wrote:

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

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

=SumByColor(A1:A10) will suffice.


Gord Dibben MS Excel MVP


On Mon, 10 Dec 2007 09:44:00 -0800, SteveDB1
wrote:

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.

"april" wrote:

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
--
aprilshowers