Help, please. I am a very occasional VBA user. I want a simple lookup value from an array, based on an input parameter. I am using a UDF prtly for learning, but also to avoid a lot of similar, complex coding in the main model. I shall have three variants of this UDF when I finish. But let me ask one question first. The VBA is given below, but does not work. The idea is to look up a value P, with approximate match, in the first column of a two dimensional array ARR (usually 6 rows x 5 columns), and return the related value from column 5. Can you show me the correction please.
Function ssrate(p As Single, arr As Range) As Single
Dim a()
Dim n As Integer
n = arr.Rows.Count
ReDim a(1 To n, 1 To 5)
a = arr
ssrate = Application.WorksheetFunction.VLookup(p, a, 1, 5)
End Function
Function ssrate(p As Single, arr As Range) As Single
Dim a()
Dim n As Integer
n = arr.Rows.Count
ReDim a(1 To n, 1 To 5)
a = arr
ssrate = Application.WorksheetFunction.VLookup(p, a, 1, 5)
End Function