Hi Paul,

It doesn't seem to give me the right answer. I think I have a problem of
format with dteDate which I declared as Date. When I use the Date From it
works, Idon't know why.

Can you help me. Thanks
Danie

" wrote:

Hi
Does dteDate always exist in Range("Revenue_Table") as you cycle
through the loop? If it does not, you will get a #VALUE! error.
If this is the problem, you will have to catch the error. This is one
way

On error resume next
For dteDate = DateFrom To DateTo
err.clear
Revenue = WorksheetFunction.HLookup(dteDate,
Range("Revenue_Table"), 21, False)
If err.number<0 then Total = Total + Revenue
Next
On error goto 0

regards
Paul

On Jun 9, 7:23 am, Danie wrote:
I have written a new function to add the values that I obtain from a Hlookup.
My code is
Function SumRev(DateFrom, DateTo)
Dim dteDate As Date
Dim Total As Double
Dim Revenue As Double

Total = 0
Revenue = 0
For dteDate = DateFrom To DateTo
Revenue = WorksheetFunction.HLookup(dteDate, Range("Revenue_Table"),
21, False)
Total = Total + Revenue
Next
SumRev = Total
End Function
I keep getting a #VALUE! error. I know the error comes from the dteDate
parameter in my Hlookup because when I change this to DateFrom it works. But
I need to add a range of values.

Can somebody help.
Thanks,
Danie