creating function with multiple arrays in arguments
 

I am trying to create a function that will act like LOOKUP but not care if
the data is in ascending order or not. TargetScore is a number like .2309,
Scorelist will also be decimal numbers like .2565, and names are just that
Names. This is what I have so far.

Function GetName(TargetScore As Variant, ScoreList() As Variant, Names() As
Variant) As Variant
On Error Resume Next
Dim i As Integer
For i = 0 To UBound(ScoreList)
If ScoreList(i).Value = TargetScore Then
GetName = Names(i).Value
End If
GetName = TargetScore
Next
Application.Volatile
End Function

I can't get this to work at all. I have tried using paramarray but it is
not able to be used with multiple arrays.

Dave Marden

creating function with multiple arrays in arguments
 

Function GETNAME(TargetScore As Variant, _
ScoreList As Range, Names As Range) As Variant
Application.Volatile
Dim v As Variant
Dim v1 As Variant
'On Error Resume Next
Dim i As Integer
v = ScoreList
v1 = Names
GETNAME = "Not Found"
For i = 1 To UBound(v, 1)
If v(i, 1) = TargetScore Then
GETNAME = v1(i, 1)
Exit Function
End If
Next
End Function


worked for me.

=getname(D2,A1:A7,B1:B7)

--
regards,
Tom Ogilvy


"Dave Marden" <Dave wrote in message
...
I am trying to create a function that will act like LOOKUP but not care if
the data is in ascending order or not. TargetScore is a number like
..2309,
Scorelist will also be decimal numbers like .2565, and names are just that
Names. This is what I have so far.

Function GetName(TargetScore As Variant, ScoreList() As Variant, Names()
As
Variant) As Variant
On Error Resume Next
Dim i As Integer
For i = 0 To UBound(ScoreList)
If ScoreList(i).Value = TargetScore Then
GetName = Names(i).Value
End If
GetName = TargetScore
Next
Application.Volatile
End Function

I can't get this to work at all. I have tried using paramarray but it is
not able to be used with multiple arrays.

Dave Marden

creating function with multiple arrays in arguments
 

Thanks Tom,
Works like a charm

Dave Marden

"Tom Ogilvy" wrote:

Function GETNAME(TargetScore As Variant, _
ScoreList As Range, Names As Range) As Variant
Application.Volatile
Dim v As Variant
Dim v1 As Variant
'On Error Resume Next
Dim i As Integer
v = ScoreList
v1 = Names
GETNAME = "Not Found"
For i = 1 To UBound(v, 1)
If v(i, 1) = TargetScore Then
GETNAME = v1(i, 1)
Exit Function
End If
Next
End Function


worked for me.

=getname(D2,A1:A7,B1:B7)

--
regards,
Tom Ogilvy


"Dave Marden" <Dave wrote in message
...
I am trying to create a function that will act like LOOKUP but not care if
the data is in ascending order or not. TargetScore is a number like
..2309,
Scorelist will also be decimal numbers like .2565, and names are just that
Names. This is what I have so far.

Function GetName(TargetScore As Variant, ScoreList() As Variant, Names()
As
Variant) As Variant
On Error Resume Next
Dim i As Integer
For i = 0 To UBound(ScoreList)
If ScoreList(i).Value = TargetScore Then
GetName = Names(i).Value
End If
GetName = TargetScore
Next
Application.Volatile
End Function

I can't get this to work at all. I have tried using paramarray but it is
not able to be used with multiple arrays.

Dave Marden