SumByColor & #NAME?

[Ty]

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

 

[Gord]

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

 

[Ty]

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





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

 

[Ty]

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

 

[Gord]

Functions do not show up under Macros.

They show up under User Defined in the Insert Function.


Gord

 

[Gord]

You don't "run" functions.


Gord

 

[Ty]

You don't "run" functions.

Gord



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

 

[Gord]

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