Problems with Hlookup

[Danie]

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

 

[paul.robinson]

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

 

[Danie]

Thanks Paul I will try it

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

 

[Danie]

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

 

[paul.robinson]

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

 

[Danie]

Thanks Paul I will try,
Danie

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