Looping INDEX-MATCH VBA with Sub or Function not defined error

Question

I have a looping INDEX-MATCH VBA that I am trying to debug, as it is constantly throwing a Sub or Function not defined error. With what I have already found on this site, I was able to check my references, but I still seem to be missing something ("Solver" is checked). At this point, an extra pair of eyes would be most helpful!

Private Sub Looping_Index_Match()
Application.ScreenUpdating = False
Dim rng As Range
Dim cell as range
Dim IndexRange as range
Dim MatchRange as range

Set rng = ActiveSheet.Range("D42:D241")

 With  Workbook("WorkCenter.xlsm").Sheets(ComboBox1.Value)
     Set IndexRange=Range(.Range("M2"),.Range(“M2”).end(xlup))
     Set MatchRange= Range(.Range("L2"),.Range(“L2”).end(xlup))
 End With

For Each cell In rng
    Cell.Offset(0,1)=Application.WorksheetFunction.Index(IndexRange,Application.WorksheetFuntion.Match(cell,Application.WorksheetFunction.Match(cell,MatchRange,0))
Next

Application.ScreenUpdating=True
End Sub

Notes: There are two workbooks involved. Data from the "Work Center" workbook column M is being retrieved and entered into the "Summary" workbook, as matched by serial numbers found in column L.

Use `option Explicit` at the top of this module, if there is a typo, this will alert you before runtime. `ComboBox1` may not be in scope? Otherwise, please indicate which line raises the error. — David Zemens, Jun 01 '15 at 20:23
Apologies, it's late in the day here. The error highlights the name of the sub itself in yellow, and "Workbook" - which I am guessing is where the code stops working. I've never used option `option Explicit` before - can you elaborate? — M.Rumery, Jun 01 '15 at 20:27
No worries, sometimes you just need another set of eyes. Was it the issue with `ComboBox1`? — David Zemens, Jun 01 '15 at 20:28
See above. This code is part of a series of subs that run from a `Userform`. — M.Rumery, Jun 01 '15 at 20:31
Unless this code is in the user form module, then combobox1 is orut of scope. Qualify it to its parent form per the answer below :) — David Zemens, Jun 01 '15 at 20:53
Discussed a similar issue yesterday: [How to fix run time error 424 when accessing the value of a CheckBox in Excel VBA?](http://stackoverflow.com/questions/30557232/how-to-fix-run-time-error-424-when-accessing-the-value-of-a-checkbox-in-excel-vb) — nicolaus-hee, Jun 02 '15 at 00:09

score 1 · Answer 1 · answered Jun 01 '15 at 20:39

i will share a quick example i built: i made a simple userform with 1 combobox and one command button. to the combobox i only added the name of the first sheet. the command button calls another macro stored in a separate module call Looping_Index, passing the combobox value

Private Sub CommandButton1_Click()
Call Looping_Index(UserForm1.ComboBox1.Value)
End Sub

Private Sub UserForm_Initialize()
ComboBox1.AddItem "Sheet1"
End Sub

Sub Looping_Index(hoja As String)

Sheets(hoja).Activate
Unload UserForm1
End Sub

this a simple example on how to work with userforms and passing values. hope it helps

Thank you. However, this code is in the user form module. Now that I've added an "s" to the end of `Workbook` (*facepalm*), the next error to debug is this: "Unable to get the Match property of the WorksheetFunction class". The code involved: `cell.Offset(0, 1) = Application.WorksheetFunction.Index(IndexRange, Application.WorksheetFuntion.Match(cell, Application.WorksheetFunction.Match(cell, MatchRange, 0)))` — M.Rumery, Jun 02 '15 at 12:10

score 0 · Answer 2 · answered Jun 02 '15 at 12:31

This code below is working for me(same module):

Private Sub CommandButton1_Click()
Dim yoursheet As Worksheet
Set yoursheet = Sheets(ComboBox1.Value)


With yoursheet
Set IndexRange = Range(.Range("M2"), .Range("M2").End(xlDown))
Set MatchRange = Range(.Range("L2"), .Range("L2").End(xlDown))

End With



ActiveSheet.Range("e42").Formula = "=index(" & yoursheet.Name & "!" & IndexRange.Address & ",match(d42" & "," & yoursheet.Name & "!" & MatchRange.Address & ",0))"
ActiveSheet.Range("e42:e241").FillDown

Unload UserForm1

End Sub

Private Sub UserForm_Initialize()
ComboBox1.AddItem "Sheet2"
End Sub

That is very close! How are you accounting for the name of the `Workbook` where the `Sheets` are located? — M.Rumery, Jun 02 '15 at 13:34

score 0 · Accepted Answer · answered Jun 02 '15 at 13:57

Private Sub CommandButton1_Click()
Dim yoursheet As Worksheet
Dim yourworkbook As Workbook


Set yourworkbook = Workbooks("Book3.xlsx")
Set yoursheet = yourworkbook.Sheets(ComboBox1.Value)


With yoursheet
Set IndexRange = Range(.Range("M2"), .Range("M2").End(xlDown))
Set MatchRange = Range(.Range("L2"), .Range("L2").End(xlDown))

End With



ActiveWorkbook.ActiveSheet.Range("e42").Formula = "=index([" & yourworkbook.Name & "]" & yoursheet.Name & "!" & IndexRange.Address & ",match(d42" & ",[" & yourworkbook.Name & "]" & yoursheet.Name & "!" & MatchRange.Address & ",0))"
ActiveWorkbook.ActiveSheet.Range("e42:e241").FillDown

ActiveWorkbook.ActiveSheet.Range("e42:e241").Copy
ActiveWorkbook.ActiveSheet.Range("e42:e241").PasteSpecial xlValues
Application.CutCopyMode = False
Unload UserForm1

End Sub

Private Sub UserForm_Initialize()
ComboBox1.AddItem "Sheet2"
End Sub

Looping INDEX-MATCH VBA with Sub or Function not defined error

3 Answers3