Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumByColor & #NAME?
I read the post from Dec 22, 2006 by vkauahi with help from Gord
Dibben in here. vkauahi "figured it out". I'm undable to figure this out. I used to use this alot. I read from the following url and downloaded the sample. I'm still unable to figure it out why I have #NAME?. I have the #NAME? when I try to use the SUMPRODUCT listed down below. http://www.excelexchange.com/SumByColor.html =SUMPRODUCT(--(COLORINDEXOFRANGE(B11:B17,FALSE, 1)=COLORINDEXOFONECELL(H7,FALSE,1))) I can see the Functions(Code). But no Macros to run. Tried copying one of the functions into the Macro to run but I still don't have a clue. Do someone have an easy answer for me using Excel 2007? Thanks in advance... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumByColor & #NAME?
Where is COLORINDEXOFRANGE function?
Where is COLORINDEXOFONECELL function? All I see at your posted URL is sumbycolor and countbycolor functions. Go to Chip Pearson's site and download the mod ColorFunctions.bas file found on this page. http://www.cpearson.com/excel/colors.aspx "You can download a module file that contains all the code on this page. The various procedures within the modColorFunctions.bas module call upon one another, so you should import the entire module into your project, rather than copying single procedures" Contains all the functions you need, including the two you are using in your formula. To add a *.bas file to your workbook, go to VBE and right-click on your workbook and select "Import File" Browse to where you have stored Chip's file, select and click OK. It will be imported as a Module. Gord Dibben Microsoft Excel MVP On Wed, 10 Aug 2011 15:51:08 -0700 (PDT), Ty wrote: I read the post from Dec 22, 2006 by vkauahi with help from Gord Dibben in here. vkauahi "figured it out". I'm undable to figure this out. I used to use this alot. I read from the following url and downloaded the sample. I'm still unable to figure it out why I have #NAME?. I have the #NAME? when I try to use the SUMPRODUCT listed down below. http://www.excelexchange.com/SumByColor.html =SUMPRODUCT(--(COLORINDEXOFRANGE(B11:B17,FALSE, 1)=COLORINDEXOFONECELL(H7,FALSE,1))) I can see the Functions(Code). But no Macros to run. Tried copying one of the functions into the Macro to run but I still don't have a clue. Do someone have an easy answer for me using Excel 2007? Thanks in advance... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumByColor & #NAME?
On Aug 10, 8:23*pm, Gord wrote:
Where is COLORINDEXOFRANGE function? Where is COLORINDEXOFONECELL function? All I see at your posted URL is sumbycolor and countbycolor functions. Go to Chip Pearson's site and download the mod ColorFunctions.bas file found on this page. http://www.cpearson.com/excel/colors.aspx "You can download a module file that contains all the code on this page. The various procedures within the modColorFunctions.bas module call upon one another, so you should import the entire module into your project, rather than copying single procedures" Contains all the functions you need, including the two you are using in your formula. To add a *.bas file to your workbook, go to VBE and right-click on your workbook and select "Import File" Browse to where you have stored Chip's file, select and click OK. It will be imported as a Module. Gord Dibben * *Microsoft Excel MVP On Wed, 10 Aug 2011 15:51:08 -0700 (PDT), Ty wrote: I read the post from Dec 22, 2006 by vkauahi with help from Gord Dibben in here. *vkauahi "figured it out". *I'm undable to figure this out. *I used to use this alot. *I read from the following url and downloaded the sample. *I'm still unable to figure it out why I have #NAME?. *I have the #NAME? when I try to use the SUMPRODUCT listed down below. http://www.excelexchange.com/SumByColor.html =SUMPRODUCT(--(COLORINDEXOFRANGE(B11:B17,FALSE, 1)=COLORINDEXOFONECELL(H7,FALSE,1))) I can see the Functions(Code). *But no Macros to run. *Tried copying one of the functions into the Macro to run but I still don't have a clue. Do someone have an easy answer for me using Excel 2007? Thanks in advance...- Hide quoted text - - Show quoted text - I have the code in Visual Basic. This is a post of me not knowing how- to use the code and execute it. I don't know how to run it. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumByColor & #NAME?
On Aug 11, 7:07*am, Ty wrote:
On Aug 10, 8:23*pm, Gord wrote: Where is COLORINDEXOFRANGE function? Where is COLORINDEXOFONECELL function? All I see at your posted URL is sumbycolor and countbycolor functions. Go to Chip Pearson's site and download the mod ColorFunctions.bas file found on this page. http://www.cpearson.com/excel/colors.aspx "You can download a module file that contains all the code on this page. The various procedures within the modColorFunctions.bas module call upon one another, so you should import the entire module into your project, rather than copying single procedures" Contains all the functions you need, including the two you are using in your formula. To add a *.bas file to your workbook, go to VBE and right-click on your workbook and select "Import File" Browse to where you have stored Chip's file, select and click OK. It will be imported as a Module. Gord Dibben * *Microsoft Excel MVP On Wed, 10 Aug 2011 15:51:08 -0700 (PDT), Ty wrote: I read the post from Dec 22, 2006 by vkauahi with help from Gord Dibben in here. *vkauahi "figured it out". *I'm undable to figure this out. *I used to use this alot. *I read from the following url and downloaded the sample. *I'm still unable to figure it out why I have #NAME?. *I have the #NAME? when I try to use the SUMPRODUCT listed down below. http://www.excelexchange.com/SumByColor.html =SUMPRODUCT(--(COLORINDEXOFRANGE(B11:B17,FALSE, 1)=COLORINDEXOFONECELL(H7,FALSE,1))) I can see the Functions(Code). *But no Macros to run. *Tried copying one of the functions into the Macro to run but I still don't have a clue. Do someone have an easy answer for me using Excel 2007? Thanks in advance...- Hide quoted text - - Show quoted text - I have the code in Visual Basic. *This is a post of me not knowing how- to use the code and execute it. *I don't know how to run it.- Hide quoted text - - Show quoted text - I figured it out. I had to exit Excel after I enabled the macros from the Macro Security. I did a "Hello World" Module that helped me kind of understand. It works now. Trying to figure out the Debug stepping. I have a Module: with Functions(countbycolor, sumbycolor) and a Module1 with showMessage "Hello World". I'm reading some more examples...etc. Seems like there is no F8 for a Function??? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumByColor & #NAME?
Functions do not show up under Macros.
They show up under User Defined in the Insert Function. Gord On Thu, 11 Aug 2011 07:10:47 -0700 (PDT), Ty wrote: Seems like there is no F8 for a Function??? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumByColor & #NAME?
You don't "run" functions.
Gord On Thu, 11 Aug 2011 05:07:37 -0700 (PDT), Ty wrote: I have the code in Visual Basic. This is a post of me not knowing how- to use the code and execute it. I don't know how to run it. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumByColor & #NAME?
On Aug 11, 9:27*am, Gord wrote:
You don't "run" functions. Gord On Thu, 11 Aug 2011 05:07:37 -0700 (PDT), Ty wrote: I have the code in Visual Basic. *This is a post of me not knowing how- to use the code and execute it. *I don't know how to run it.- Hide quoted text - - Show quoted text - Gord, Correct. Functions are not ran. I'm still learning or refreshing my memory and reading. "..A function is a pre-set formula which can be written directly into a cell, to display an outcome....etc..." Gord! I don't see function(or User Defined) in Visual Basic. I had to copy(drag it) the function I needed in this thread from the sample to my VBAProject. When I click on Insert, I have: 1. Procedure 2. UserForm 3. Module 4. Class Module 5. File After I dragged it, I right click on Functions and it has a REMOVE Functions. The Function shows up under Modules and then Functions in the Sample. I searched the Excel help.. It has an example"Writing a Function Procedure" with a sub that has a function but do not say how to create a function. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumByColor & #NAME?
Get out of VBE and back to the Excel window.
On the Toolbar click on the fx icon. That is where you find "user defined" list of functions. Whichever UDF's you have installed in your workbook or add-in will show up there. You should also be able to find them in a module when in VBE. Gord On Thu, 11 Aug 2011 14:21:52 -0700 (PDT), Ty wrote: On Aug 11, 9:27*am, Gord wrote: You don't "run" functions. Gord On Thu, 11 Aug 2011 05:07:37 -0700 (PDT), Ty wrote: I have the code in Visual Basic. *This is a post of me not knowing how- to use the code and execute it. *I don't know how to run it.- Hide quoted text - - Show quoted text - Gord, Correct. Functions are not ran. I'm still learning or refreshing my memory and reading. "..A function is a pre-set formula which can be written directly into a cell, to display an outcome....etc..." Gord! I don't see function(or User Defined) in Visual Basic. I had to copy(drag it) the function I needed in this thread from the sample to my VBAProject. When I click on Insert, I have: 1. Procedure 2. UserForm 3. Module 4. Class Module 5. File After I dragged it, I right click on Functions and it has a REMOVE Functions. The Function shows up under Modules and then Functions in the Sample. I searched the Excel help.. It has an example"Writing a Function Procedure" with a sub that has a function but do not say how to create a function. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumbycolor formula | Excel Worksheet Functions | |||
SumByColor Stopped Working | Excel Worksheet Functions | |||
how to use sumbycolor fuction? | Excel Worksheet Functions |