Error with Index ... Match formula

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am getting an error 'Sub or Function Not defined" on the line highlighted below:

Rich (BB 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
Rich (BB 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?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Try

Code:
NRID = .Range("B10").Value = Application.Index(.Range("A:A"), Match(.Range("B10"), .Range("A:A"), 0) + 1)
 
Upvote 0
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.
 
Upvote 0
Silly me

Rich (BB code):
NRID = .Range("B10").Value = Application.Index(.Range("A:A"), Application.Match(.Range("B10"), .Range("A:A"), 0) + 1)
 
Upvote 0
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.<function>" (as in this case), vs. "application.worksheet.<function>" 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.
 
Upvote 0
Ok ... thanks for the good read. Appreciate your help!
 
Upvote 0
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
 
Upvote 0
Both these versions work.

Rich (BB 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

Rich (BB 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.)

Rich (BB code):
NRID = .Range("B10").Value = Application.WorksheetFunction.Index("A:A", Match("B10", "A:A", 0) + 1)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top