#VALUE error in private function

[Afjio12]

Hello,

I use the code below to count the empty cells and with a certain fill color.
If I select a few cells the function works, but if i select all the cells i need i get the #VALUE error.
I think this is because my cells have al kinds of format categories like general, text and number.
Does anyone know how i can solve this error?

Tx!

Please Login or Register  to view this content.

[rorya]

How are you calling the function when you get the error?

[Afjio12]

Just in a cell formula. =CountCells(range)

[rorya]

What range? Are there any error values in the range, or merged cells?

[Afjio12]

A named range. No there are no error values in the range and no merged cells

[rorya]

Perhaps you could post a sample workbook to make life easier?

[Afjio12]

ah you were right. There is an error in the named range. Is there a way to ignore errors in the function? It is a very big data sheet and there will always be a few errors.

[rorya]

Assuming you want to ignore error cells, you might use:

Please Login or Register  to view this content.

[Afjio12]

Yeah that'll work. I now have a second problem, the cell is only opdated when one of the cells in the named range is changed. I need it to be updated when another macro is called.
I tried the calculate now option(F9) but that doesn't work. Do you know a sollution to this?

[rorya]

The ideal is to stop using colours as data. But if you can't do that, you'll have to make the function volatile by adding:

Please Login or Register  to view this content.

after the declaration line.

[Afjio12]

What is the declaration line?

[rorya]

The first one:

Please Login or Register  to view this content.

[Afjio12]

That doesn't work either. I don't know why but none of the re-calculate functions work

[rorya]

It should work, though you may have to just edit and re-enter one of the formula cells to force the code to be recompiled.

[Afjio12]

Ah yes it works now. Tx!

[rorya]

Glad to help. Thanks for the rep.

[Afjio12]

Hello Guys,

Another problem occured... When there is no error in the range i still get the #VALUE error. After a bit of testing it appears that it is not the cell format that is the problem. I used the function multiple times with different named ranges. When i enter a value in ome cell all the different functions return #VALUE. Do you guys have any idea what can be the reason that triggers the errors?

Tx!

[rorya]

I suggest you post a sample workbook showing the problem.

[shg]

Suggestion:

Please Login or Register  to view this content.

If applied to a large range, that's going to be pretty brutal on performance.

[Afjio12]

Unfortunately the same problem occurs with this sollution. Tx anyway!