match in one range to extract value from another

**jalea148** · 06-01-2012, 09:00 AM

I'm sure this can be done better. Thisdate is a set of dates in, say a1 to a100. in b1 to b100 there are values [the val range]. I'm trying to return the value in val that corresponds to thisdate matching startdate.

this code produces an error.

Function INIT(THISDATE As Range, STARTDATE As Date, VAL As Range)
Dim C, B
Dim ICOUNT As Integer, MAX As Integer
Dim ARR1()
Dim ARR2()
MAX = THISDATE.Count
ReDim ARR1(1 To MAX)
ReDim ARR2(1 To MAX)

ARR1 = Array(THISDATE)
ARR2 = Array(VAL)
For ICOUNT = 1 To MAX
If DateValue(ARR1(ICOUNT)) = STARTDATE Then
INIT = ARR2(ICOUNT)
Exit Function
End If
Next ICOUNT
End Function

**StevenM** · 06-01-2012, 10:21 AM

Which line is the error?

**jalea148** · 06-01-2012, 11:09 AM

Originally Posted by StevenM

Which line is the error?

I don't know. The spreadsheet only shows #Value!

**tigeravatar** · 06-01-2012, 10:54 AM

jalea148,

In the future, please wrap your code in code tags per the forum rules (link in my sig for how).
As to your question, I'm not sure why you're trying to use a UDF when Excel has several native functions that can perform what you're trying to do. The most popular for this task is the VLookup() function. Using your stated example, it would be:

=VLOOKUP(STARTDATE,A1:B100,2,FALSE)

Where STARTDATE is the date to match.

**StevenM** · 06-01-2012, 11:29 AM

As for your code.
Delete the ReDim statements.
Omit the Array() so that: ARR1 = THISDATE and ARR2 = VAL
And then, assuming the ranges are columns use: DateValue(ARR1(ICOUNT, 1)) = STARTDATE Then
And: INIT = ARR2(ICOUNT, 1)

The point here is that you can write a range to a variant, and the variant will become a two-dimensional array (even if the range is only one row, or only one column).

**jalea148** · 06-01-2012, 05:11 PM

Originally Posted by StevenM

As for your code.
Delete the ReDim statements.
Omit the Array() so that: ARR1 = THISDATE and ARR2 = VAL
And then, assuming the ranges are columns use: DateValue(ARR1(ICOUNT, 1)) = STARTDATE Then
And: INIT = ARR2(ICOUNT, 1)

The point here is that you can write a range to a variant, and the variant will become a two-dimensional array (even if the range is only one row, or only one column).

Sweet - works fine. Thanx