Unable to get Match property of the worksheetfunction class

[JimEH]

I have written VBA code to use 4 different dropdown list, then the user of the form selects multiple items. Based upon the selections a match function obtains a code. Everything works except the multiselect depression input section of code. The code continues to elicit the "unable to get match property", the other 3 input sections work perfectly. Please advise?

The formulae works for 3 of the 4 dropdown list. Here is the code:

' add multiselect depression input

Dim i As Integer
Dim Bin As String
Bin = ListBoxDep.List(i)

For i = 0 To ListBoxDep.ListCount - 1
If ListBoxDep.Selected(i) Then
With Sheets(2)
Range("V" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveCell.Value = ListBoxDep.List(i)
'Bin = ListBoxDep.List(i)
Range("W" & Rows.Count).End(xlUp).Offset(1, 0).Select
'ActiveCell.Value = Application.WorksheetFunction.VLookup(Bin, Sheet2.Range("O82:P95"), 2, False)
ActiveCell.Value = Application.WorksheetFunction.Index(Sheet2.Range("P82:P95"), Application.WorksheetFunction.Match(ListBoxAdv.List(i), Sheet2.Range("O82:O95"), 0))

End With
End If
Next i

' add Adj Anxiety multiinput data

For i = 0 To ListBoxAdv.ListCount - 1
If ListBoxAdv.Selected(i) Then
With Sheets(2)
Range("V" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveCell.Value = ListBoxAdv.List(i)
Range("W" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveCell.Value = Application.WorksheetFunction.Index(Sheet2.Range("P51:P62"), Application.WorksheetFunction.Match(ListBoxAdv.List(i), Sheet2.Range("O51:O62"), 0))

End With
End If
Next i

' add Psy Substance multiinput data

For i = 0 To ListBoxPsy.ListCount - 1
If ListBoxPsy.Selected(i) Then
With Sheets(2)
Range("V" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveCell.Value = ListBoxPsy.List(i)
Range("W" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveCell.Value = Application.WorksheetFunction.Index(Sheet2.Range("P65:P80"), Application.WorksheetFunction.Match(ListBoxPsy.List(i), Sheet2.Range("O65:O80"), 0))
End With
End If
Next i

' add cognitive and other multiinput data

For i = 0 To ListBoxCog.ListCount - 1
If ListBoxCog.Selected(i) Then
With Sheets(2)
Range("V" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveCell.Value = ListBoxCog.List(i)
Range("W" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveCell.Value = Application.WorksheetFunction.Index(Sheet2.Range("P34:P47"), Application.WorksheetFunction.Match(ListBoxCog.List(i), Sheet2.Range("O34:O47"), 0))
End With
End If
Next i

' Add diagnosis to list

Range("V" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveCell.Value = TextBoxCPT.Value

End Sub

 

[Fluff]

The Anxiety may be caused by depression

Alternatively it may be that you are using the wrong listbox in the depression code

For i = 0 To [COLOR=#ff0000]ListBoxDep[/COLOR].ListCount - 1
   If [COLOR=#ff0000]ListBoxDep[/COLOR].Selected(i) Then
      With Sheets(2)
         Range("V" & Rows.count).End(xlUp).Offset(1, 0).Select
         ActiveCell.Value = [COLOR=#ff0000]ListBoxDep[/COLOR].List(i)
         Range("W" & Rows.count).End(xlUp).Offset(1, 0).Select
         ActiveCell.Value = Application.WorksheetFunction.Index(Sheet2.Range("P82:P95"), Application.WorksheetFunction.Match([COLOR=#0000ff]ListBoxAdv[/COLOR].List(i), Sheet2.Range("O82:O95"), 0))

      End With
   End If
Next i

 

[JimEH]

You are a lifesaver, I have been looking at this for several days! Such a obvious error. Thank you

 

[Fluff]

Glad to help & thanks for the feedback