converted range to array; error Invalid Qualifier

[jalea148]

Here's my code. I suspect this can be done better without converting to an array.

Function Retval(RangeDates As Range, RangeValues As Range, Testdate)
Dim ARR_RD() As Integer, ARR_RV() As Integer
Dim Lb As Integer, Ub As Integer, I As Integer

Retval = 0
ARR_RD = RangeDates.Value
ARR_RV = RangeValues.Value
Ub = UBound(ARR_RD)
Lb = LBound(ARR_RD)
Retval = 0

Debug.Print Lb, Ub, Testdate
Debug.Print ARR_RD(1).Value, ARR_RD(2), ARR_RD(3), ARR_RD(4)

For I = Lb + 1 To Ub
  If Testdate >= ARR_RD(I - 1) And Testdate < ARR_RD(I) Then
    Retval = ARR_RV(I - 1)
    Exit For
  End If
Next I
End Function

Immediate window:

1 149 12/4/2012
[2nd debug.print doesn't print anything]

 

[p45cal]

Are the RangeDates and RangeValues ranges vertical ranges (or horizontal)?
If you want them to be either, then yes, it might be easier not to convert to arrays, but if you have many cells with this function in and/or the ranges referred to are very large then it might get resource hungry if you abandon the conversion to arrays.
Statements such as ARR_RD = RangeDates.Value produce 2 dimensional arrays so you need either to refer to each member with 2 arguments (ARR_RD(2, 1)) or convert the arrays to 1 dimension.
Also you'll probably get further if you change:
Dim ARR_RD() As Integer, ARR_RV() As Integer
to:
Dim ARR_RD, ARR_RV

 

[jalea148]

Are the RangeDates and RangeValues ranges vertical ranges (or horizontal)?
If you want them to be either, then yes, it might be easier not to convert to arrays, but if you have many cells with this function in and/or the ranges referred to are very large then it might get resource hungry if you abandon the conversion to arrays.
Statements such as ARR_RD = RangeDates.Value produce 2 dimensional arrays so you need either to refer to each member with 2 arguments (ARR_RD(2, 1)) or convert the arrays to 1 dimension.
Also you'll probably get further if you change:
Dim ARR_RD() As Integer, ARR_RV() As Integer
to:
Dim ARR_RD, ARR_RV

Please show me the code using only ranges. The ranges are vertical, width 1 , with < 200 rows.
Thank you

 

[p45cal]

Please show me the code using only ranges. The ranges are vertical, width 1 , with < 200 rows.
Thank you

Yes, OK, I will. Could you put together a little spreadsheet with typical data in which would you'd expect to return non-zero values with the function and attach it here? Saves us guessing wrong and wasting both our time.

Without checks and balances, not robust, no bells and whistles, it might be:

Function RetVal(RangeDates As Range, RangeValues As Range, Testdate)
RetVal = 0
For i = 2 To RangeDates.Cells.Count
  If Testdate >= RangeDates.Cells(i - 1).Value And Testdate < RangeDates.Cells(i).Value Then
    RetVal = RangeValues.Cells(i - 1).Value
    Exit For
  End If
Next i
End Function

It might be better to use built-in worksheet formula to achieve this though:
=INDEX($B$2:$B$129,MATCH($F2,$A$2:$A$129))
seems to give the same result as:
=Retval($A$2:$A$129,$B$2:$B$129,$F2)
given the dates are in ascending order.

 

[jalea148]

It might be better to use built-in worksheet formula to achieve this though:
=INDEX($B$2:$B$129,MATCH($F2,$A$2:$A$129))
seems to give the same result as:
=Retval($A$2:$A$129,$B$2:$B$129,$F2)
given the dates are in ascending order.

Thank you. This works perfectly.

 

[snb]

The code you posted has quite another purpose than what you were looking for.

 

[jalea148]

The code you posted has quite another purpose than what you were looking for.

Based on your suggestion I adjusted it to meet my needs. Thanx again.