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]

What sort of values do you have in the listbox?
It sounds as though there is no matching values in your range

 

[JimEH]

The values are what is listed in the listbox as follows (once selected the item,I would like it to return the code in column 2):

Major depressive disorder, Recurrent episode, In full remission	F33.42
Major depressive disorder, Recurrent episode, In partial remission	F33.41
Major depressive disorder, Recurrent episode, Mild	F33.0
Major depressive disorder, Recurrent episode, Moderate	F33.1
Major depressive disorder, Recurrent episode, Severe	F33.2
Major depressive disorder, Recurrent episode, With psychotic features	F33.3
Major depressive disorder, Single episode, In full remission	F32.5
Major depressive disorder, Single episode, In partial remission	F32.4
Major depressive disorder, Single episode, Mild	F32.0
Major depressive disorder, Single episode, Moderate	F32.1
Major depressive disorder, Single episode, Severe	F32.2
Major depressive disorder, Single episode, With Psychotic Symptoms	F32.3
Other specified depressive disorder	F32.8
Persistent depressive disorder (dysthymia)	F34.1

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Fluff]

Do you have those EXACT same values in Range O82:O95 in Sheet2?

 

[JimEH]

Yes, it is the exact values (dropdown list is the same as the look up list)

 

[Fluff]

Ok,try adding the lines in blue

Dim i As Integer
Dim Bin As String
Bin = ListBoxDep.List(i)
[COLOR=#0000ff]Dim Var As Variant[/COLOR]

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)
         Range("W" & Rows.count).End(xlUp).Offset(1, 0).Select
         [COLOR=#0000ff]Var = Application.Match(ListBoxAdv.List(i), Sheet2.Range("O82:O95"), 0)
         Debug.Print Var[/COLOR]
         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

When you get the error look at the immediate window & what is the last entry?
If the Immediate window is not visible (normally below the code window) Ctrl G will bring it up

 

[JimEH]

I get "Error 2042"

 

[Fluff]

In that case it cannot find the value in the listbox.
If you change the debug line to

Debug.Print var, "|" & ListBoxAdv.List(i) & "|"

It will show you the listbox value as well, wrapped in a pair of | to show if you have any leading/trailing spaces.

 

[JimEH]

Very interesting, when I select some items from the depression box, but it gives me data from the anxiety box, which is a different range P51:P62, NOT the P82:P95 range. This makes no sense to me.

 

[JimEH]

When I press a depreesion diagnosis it continues to give an anxiety text and code, The depression code refers to row 82 to 95 and the anxiety code refers to row 51 to 62; but it seem that somehow the depression code has a mind of its own and selects the aniety codes.