WorksheetFunction.Match can't find a match

[Bagharmin]

I have a ComboBox1 on a UserForm that is populated from a list of clients found on a worksheet located in the same workbook. My goal is to use the ComboBox1_Change() event to take the item selected from ComboBox1, find it on the list used to populate it, and (eventually) use that result to get additional information on the selection. However, I can't seem to find a match. Here's the code:

Private Sub ComboBox1_Change()
Dim ClientFSRs As Range, ClientCombos As Range, CB7Scan As Integer, FSRFind As Double
    With Worksheets("Clients")
        Set ClientFSRs = .Range(.Cells(3, 9), .Cells(.Range("C1") + 2, 9))
        Set ClientCombos = .Range(.Cells(3, 4), .Cells(.Range("C1") + 2, 9))
    End With
    
    Select Case ComboBox1.ListIndex
        Case Is > 2
            For CB7Scan = 0 To 4
                FSRFind = Application.WorksheetFunction.Match(ComboBox1.List(ComboBox1.ListIndex), ClientCombos, 0)
                If ComboBox7.List(CB7Scan) = Application.WorksheetFunction.Index(ClientFSRs, FSRFind, 1) Then
                    ComboBox7.ListIndex = CB7Scan
                    Exit Sub
                End If
            Next CB7Scan
            ComboBox7.ListIndex = 0
    End Select
End Sub

I keep getting an error at the "FSRFind = " line, saying it can't find a match. I'm having a hard time seeing how that can be since all the items in ComboBox1 came directly from the ClientCombos range. Can anyone tell me what simple thing I've forgotten here?

Thanks.

 

[ChrisM]

There's always 5 different ways to do something in VBA, but I'm a little confused about your range definition:

Set ClientCombos = .Range(.Cells(3, 4), .Cells(.Range("C1") + 2, 9))

This seems overly complex. You are not using any variables, so why all the difficult coding? Why not just:

Set ClientCombos = Worksheets("Clients").Range("A1:C10") 'or whatever range you need

I've never seen a range defined like you did above. When I do an ADDRESS on it in the immediate window, it comes out like this:

print sheet1.Range(sheet1.Cells(3, 4), sheet1.Cells(sheet1.Range("C1") + 2, 9)).Address = $D$2:$I$3

Unless you're using a multicolumn combobox, most of your combo ranges should be a single column right?

Try reworking your range definition and see if that fixes it. If that does, here are a couple more points:

1. FSRFind shouldn't be a Double, make it a Long

2. Consider making a multicolumn combobox. You don't have to show the extra columns to the user, but you can use them as hidden reference to yourself, and instead of having to do a FIND to get lookup info, you can just pull the values from columns 2,3,...xxx.

3. Couldn't really tell why you are using the Select...Case and the For...Next loops. If you just want the value of the combobox, use Combobox1.Value. There is no need to loop through everything.

 

[Bagharmin]

I didn't understand why you were talking about a multi-column combo box until I re-read your post carefully. Then I went back to my original post to see if you had made a typo or if I had. As it turns out, my original code was in error.

Set ClientCombos = .Range(.Cells(3, 4), .Cells(.Range("C1") + 2, 9))

should actually be

Set ClientCombos = .Range(.Cells(3, 4), .Cells(.Range("C1") + 2, 4))

As you can imagine, changing that 9 to the 4 it was supposed to be made ALL the difference and everything now works as I wanted it to.

And the reason for the .Range("C1") part is that the range is dynamic and the rows increase or decrease on a monthly basis. C1 is a placeholder I use so I know how many rows I am using.

Sorry for all the trouble.