Results 1 to 9 of 9

Error with Index ... Match formula

This is a discussion on Error with Index ... Match formula within the Excel Questions forums, part of the Question Forums category; I am getting an error 'Sub or Function Not defined" on the line highlighted below: Code: Sub Button1_Click() Dim wsh1 ...

  1. #1
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    2,676

    Default Error with Index ... Match formula

    I am getting an error 'Sub or Function Not defined" on the line highlighted below:

    Code:
    Sub Button1_Click()
    Dim wsh1 As Worksheet
    Dim CRID As Integer
    Dim PRID As Integer
    Dim NRID As Integer
    
    Set wsh1 = Worksheets("sheet1")
    
    With wsh1
        NRID = .Range("B10").Value = Application.WorksheetFunction.Index("A:A", Match("B10", "A:A", 0) + 1)
        PRID = .Range("B10").Value = Application.WorksheetFunction.Index("A:A", Match("B10", "A:A", 0) - 1)
        .Range("B11").Value = PRID
        .Range("B12").Value = NRID
    End With
    End Sub
    I tried
    Code:
    NRID = .Range("B10").Value = Application.WorksheetFunction.Index("A:A", Application.WorksheetFunction.Match("B10", "A:A", 0) + 1)
    with no luck receiving only 'Unable to get the Match property of the WorksheetFunction class'.

    Is anyone able to provide any advice?

  2. #2
    VoG
    VoG is offline
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651

    Default Re: Error with Index ... Match formula

    Try

    Code:
    NRID = .Range("B10").Value = Application.Index(.Range("A:A"), Match(.Range("B10"), .Range("A:A"), 0) + 1)
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    2,676

    Default Re: Error with Index ... Match formula

    Hi Peter ....

    My ammended code ...
    Code:
    NRID = .Range("B10").Value = Application.Index(.Range("A:A"), Match(.Range("B10"), .Range("A:A"), 0) + 1)
    Still netting 'Sub or Function Not defined' error highlighting Match.

  4. #4
    VoG
    VoG is offline
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651

    Default Re: Error with Index ... Match formula

    Silly me

    Code:
    NRID = .Range("B10").Value = Application.Index(.Range("A:A"), Application.Match(.Range("B10"), .Range("A:A"), 0) + 1)
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    2,676

    Default Re: Error with Index ... Match formula

    Thanks Peter,

    I'm getting NRID value calculation of 0. When I step through the code, although I'm not getting errors, hovering over the variable I get '.range("B10").value=******** variable or With block variable not set>'

    And as I'm learning, are there simple rules of thumb on knowing when to use "application." (as in this case), vs. "application.worksheet." like I used elsewhere in my code, vs. not using either when coding? Don't spend too much time answering, it is likely quite in depth.

  6. #6
    VoG
    VoG is offline
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651

    Default Re: Error with Index ... Match formula

    I'm not sure why you are getting an error.

    Application vs WorksheetFunction Daily Dose of Excel Blog Archive The WorksheetFunction Method
    HTH, Peter
    Please test any code on a copy of your workbook.

  7. #7
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    2,676

    Default Re: Error with Index ... Match formula

    Ok ... thanks for the good read. Appreciate your help!

  8. #8
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    2,676

    Default Re: Error with Index ... Match formula

    I took out the With wsh1 / End With and adjusted for formula accordingly. I no longer get the quasi error, but the values continue to be returned as 0.
    Anyone else out there wish to take a stab at it?

    Code:
    Sub Button1_Click()
    Dim wsh1 As Worksheet
    Dim CRID As Integer
    Dim PRID As Integer
    Dim NRID As Integer
    
    Set wsh1 = Worksheets("sheet1")
    
       PRID = Range("B10").Value = Application.Index(Range("A1:A7"), Application.Match(Range("B10"), Range("A1:A7"), 0) + 1)
       NRID = Range("B10").Value = Application.Index(Range("A1:A7"), Application.Match(Range("B10"), Range("A1:A7"), 0) - 1)
       Range("B11") = PRID
       Range("B12") = NRID
    
    End Sub

  9. #9
    Board Regular
    Join Date
    Apr 2005
    Location
    Ontario, Canada
    Posts
    2,676

    Default Re: Error with Index ... Match formula

    Both these versions work.

    Code:
    Sub Button1_Click()
    Dim wsh1 As Worksheet
    Dim work As Integer
    Dim pwork As Integer
    Dim nwork As Integer
    
    Set wsh1 = Worksheets("sheet1")
    
    With wsh1
       .Range("B12").Value = Application.Index(Range("A1:A7"), Application.Match(Range("B10"), Range("A1:A7"), 0) + 1)
       .Range("B11").Value = Application.Index(Range("A1:A7"), Application.Match(Range("B10"), Range("A1:A7"), 0) - 1)
    End With
    End Sub
    Code:
    Sub Button1_Click()
    Dim wsh1 As Worksheet
    Dim work As Integer
    Dim pwork As Integer
    Dim nwork As Integer
    
    Set wsh1 = Worksheets("sheet1")
    
    With wsh1
       nwork = Application.Index(Range("A1:A7"), Application.Match(Range("B10"), Range("A1:A7"), 0) + 1)
       pwork = Application.Index(Range("A1:A7"), Application.Match(Range("B10"), Range("A1:A7"), 0) - 1)
       .Range("B11").Value = pwork
       .Range("B12").Value = nwork
    End With
    End Sub
    I guess it didn't like the highlighted portion of the original code. (It didn't look right.)

    Code:
    NRID = .Range("B10").Value = Application.WorksheetFunction.Index("A:A", Match("B10", "A:A", 0) + 1)

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com