|
Problems with Hlookup
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 |
Problems with Hlookup
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 |
Problems with Hlookup
Thanks Paul I will try it
" 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 |
Problems with Hlookup
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 |
Problems with Hlookup
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 - |
Problems with Hlookup
Thanks Paul I will try,
Danie " wrote: 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 - |
| All times are GMT +1. The time now is 07:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com