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