Consulting

Results 1 to 5 of 5

Thread: Help with Index Custom Function

  1. #1
    VBAX Tutor david000's Avatar
    Joined
    Mar 2007
    Location
    Chicago
    Posts
    275
    Location

    Unhappy Help with Index Custom Function

    [vba]
    Function xIndex(arr1, arr2, arr3)

    With Application.WorksheetFunction

    xIndex = .Index(arr1, .Match(arr2, arr3, 0))
    End With

    'I need an additional INDEX but with a missing ROW argument!
    'works on the sheet not in the code???

    '=xindex($D$2:$D$3,K2,INDEX(E2:F3,,2))
    End Function

    [/vba]

    I really really want to add an additional Index into this Custom Function, but I can't get around the required ROW argument for index in VB where it's not required in Excel (at least you can skip it!)?!

    Or ANYTHING that allows me to choose the column dosen't have to be INDEX.



    I'm sure someone has this answer in thier trick bag! So, give it up please!

    Thanx!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,142
    Location
    Sorry David, I am not getting it. The XINDEX function in the ws returns a result, so what exactly are you trying to add?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Tutor david000's Avatar
    Joined
    Mar 2007
    Location
    Chicago
    Posts
    275
    Location

    Thanks - For taking a look I'll try my hardest to xplain.

    [vba]
    Function xIndex2(arr1, arr2, arr3, arr4)

    With Application.WorksheetFunction

    xIndex2 = .Index(arr1, .Match(arr2, .Index(arr3, , arr4)), 0) 'arr4 selects the column.
    End With

    'I'm trying to do this formula below. I get an "Arugument Not Optional Error" -
    'When I Skip the ROW Arrgument in place of the Column one.
    '=INDEX(D23,MATCH(I2,INDEX(E2:F3,,1),0))< Empty Row Argument Causing ERROR. In the VB Only...Not the Worksheet

    'I need an additional INDEX but with a missing ROW argument!
    'works on the sheet not in the code???
    End Function


    [/vba]

    This link Example3 is what I'm mainly trying to do.

    Link >>> contextures.com/xlFunctions03.html#IndexMatch2


    I'm really not a huge fan of SUMPRODUCT, because, I can never remember when to use that "--" operator!!! At least with, "Index - text" , "Match- number", you know what to expect.

    Exept in this case of course!

    I've been over thinking this dumb issue too long.


    I'm posting a new sample it's as complete as I can make it...

    I'm really sorry about any confusion I may have casued - I'm dumb


    Attachment 6156< New Example!!!!
    Last edited by david000; 07-06-2007 at 01:17 PM.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,142
    Location
    [vba]

    Function xIndex2(arr1, arr2, arr3, arr4)
    With Application.WorksheetFunction
    On Error Resume Next
    xIndex2 = .Index(arr1, .Match(arr2, .Index(arr3, 0, arr4), 0))
    On Error GoTo 0
    If IsEmpty(xIndex2) Then xIndex2 = ""
    End With
    End Function
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Tutor david000's Avatar
    Joined
    Mar 2007
    Location
    Chicago
    Posts
    275
    Location

    Dude! YOU ARE GREAT!

    Thank you,

    I could not figure that out!

    I?m so grateful for your help - I just got so frustrated


    I thought I was going CRAZY!




Posting Permissions

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