Hi
dates are often problematic. If your dates in the table are in the
format "dd mmm yy" e.g. "23 Mar 08" then try using

fomat(Datevalue(dteDate),"dd mmm yy")

instead of dteDate in the Hlookup,so that you are looking for a string
in HLookup rather than a date. Obviously, use the date format you have
rather than my example.

regards
Paul

On Jun 9, 11:17*am, Danie wrote:
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- Hide quoted text -

- Show quoted text -