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):
[TABLE="width: 635"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Major depressive disorder, Recurrent episode, In full remission[/TD]
[TD]F33.42[/TD]
[/TR]
[TR]
[TD]Major depressive disorder, Recurrent episode, In partial remission[/TD]
[TD]F33.41[/TD]
[/TR]
[TR]
[TD]Major depressive disorder, Recurrent episode, Mild[/TD]
[TD]F33.0[/TD]
[/TR]
[TR]
[TD]Major depressive disorder, Recurrent episode, Moderate[/TD]
[TD]F33.1[/TD]
[/TR]
[TR]
[TD]Major depressive disorder, Recurrent episode, Severe[/TD]
[TD]F33.2[/TD]
[/TR]
[TR]
[TD]Major depressive disorder, Recurrent episode, With psychotic features[/TD]
[TD]F33.3[/TD]
[/TR]
[TR]
[TD]Major depressive disorder, Single episode, In full remission[/TD]
[TD]F32.5[/TD]
[/TR]
[TR]
[TD]Major depressive disorder, Single episode, In partial remission[/TD]
[TD]F32.4[/TD]
[/TR]
[TR]
[TD]Major depressive disorder, Single episode, Mild[/TD]
[TD]F32.0[/TD]
[/TR]
[TR]
[TD]Major depressive disorder, Single episode, Moderate[/TD]
[TD]F32.1[/TD]
[/TR]
[TR]
[TD]Major depressive disorder, Single episode, Severe[/TD]
[TD]F32.2[/TD]
[/TR]
[TR]
[TD]Major depressive disorder, Single episode, With Psychotic Symptoms[/TD]
[TD]F32.3[/TD]
[/TR]
[TR]
[TD]Other specified depressive disorder[/TD]
[TD]F32.8[/TD]
[/TR]
[TR]
[TD]Persistent depressive disorder (dysthymia)[/TD]
[TD]F34.1
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.