Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Color Recognition and Summing of Data Within a Specified Colo | Excel Worksheet Functions | |||
Summing Cells by Color | Excel Discussion (Misc queries) | |||
Summing fields based on color | Excel Discussion (Misc queries) | |||
Color Cells Summing | Excel Worksheet Functions | |||
Summing Cells with a BLUE font (or any other color) | Excel Programming |