+ Reply to Thread
Results 1 to 3 of 3

creating function with multiple arrays in arguments

  1. #1
    Dave Marden
    Guest

    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

  2. #2
    Tom Ogilvy
    Guest

    Re: 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 [email protected]> wrote in message
    news:[email protected]...
    > 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




  3. #3
    Dave Marden
    Guest

    Re: 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 [email protected]> wrote in message
    > news:[email protected]...
    > > 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

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1