Adding cell values via a function results in #VALUE!

[davehartleyuk]

Hello all and thanks for helping and reading my first post

I'm using Excel 2010.

I have a workbook with quite a few sheets on it so to simplify my problem I'll concentrate on just two worksheets, one called Ackroyds and the other Supplier Details.

What I'm doing is a simple invoice spreadsheet where on the Ackroyds sheet I input the invoice information and it lets me know when that invoice needs paying by highlighting it red. All working great.

On the supplier page I have a list of all the companies I deal with and one of the columns is Total Due Now. In this column I want it to look at the list of invoices for that supplier (in this example Ackroyds) and add up all the ones that are red so I can see the total amount due now (some companies give us 30 days to pay so wouldn't be included if still in grace period) to that company. Having battled with trying to loop through the cells in the column to see if they were red and discovering that reading the colour of conditional formatted cells is pretty much impossible, I have it checking the same formula used in the conditional formatting rules....again...all working great.

This is my function

Please Login or Register  to view this content.

and I call it using

Please Login or Register  to view this content.

So it loops through the range of dates to check if the invoice is due in the next 3 days. If it is I want it to add it to the variable tmpAmnt....this is where it errors. If I have the line

Please Login or Register  to view this content.

set to

Please Login or Register  to view this content.

then it correctly fills the cell with the last value of the range. It's only when I try to add them up I get the #VALUE! error.

Any takers to this problem?

Many thanks

Dave

[:) Sixthsense :)]

Try this...

Please Login or Register  to view this content.

[davehartleyuk]

Thanks for the reply but that didn't fix the problem.

I've solved it though. It was caused by me trying to add up a range and not a single cell value.

Here is the modified code:

Please Login or Register  to view this content.

Thanks again