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.
How are you calling the function when you get the error?
Everyone who confuses correlation and causation ends up dead.
Just in a cell formula. =CountCells(range)
What range? Are there any error values in the range, or merged cells?
A named range. No there are no error values in the range and no merged cells
Perhaps you could post a sample workbook to make life easier?
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.
Assuming you want to ignore error cells, you might use:
Please Login or Register to view this content.
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?
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:
after the declaration line.Please Login or Register to view this content.
What is the declaration line?
The first one:
Please Login or Register to view this content.
That doesn't work either. I don't know why but none of the re-calculate functions work
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.
Ah yes it works now. Tx!
Glad to help. Thanks for the rep.
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!
Last edited by Afjio12; 04-23-2019 at 07:32 AM.
I suggest you post a sample workbook showing the problem.
Suggestion:
If applied to a large range, that's going to be pretty brutal on performance.Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
Unfortunately the same problem occurs with this sollution. Tx anyway!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks