Problems with WorksheetFunction.Match
I'm guessing that one of your variables is not what you think it is. Have
you done a debug.print, or used a message box to chect the values of the
variables. You can also use the locals window in VBA.
"Luke" wrote in message
...
I'm really just wanting to know WHY this doesn't work, although solutions
are
always welcome. Here is the code:
Private Sub ComboBox1_Change()
Dim MaxEEs As Long, LastEERow As Long, TotalEEs As Long, ClientStart As
Long
Dim ClientNum As Integer, EEList As Long, AllClients As Range
MaxEEs = Worksheets("Employees").Range("P1")
LastEERow = MaxEEs + 2
With UserForm2.ComboBox2
.AddItem ("All Employees")
If UserForm2.ComboBox1.ListIndex = 0 Then
TotalEEs = MaxEEs
ClientStart = 1
End If
ClientNum = Val(Left(UserForm2.ComboBox1.Value, 4))
AllClients = Worksheets("Employees").Range(Cells(3, 1),
Cells(LastEERow, 1))
ClientStart = Application.WorksheetFunction.Match(ClientNum,
AllClients, 0)
TotalEEs = Application.WorksheetFunction.CountIf(Worksheets
_("Employees").Range(Cells(3, 1), Cells(LastEERow, 1)), ClientNum)
For EEList = ClientStart To TotalEEs
.AddItem (Worksheets("Employees").Range("A2").Offset(EEList ,
2))
Next EEList
End With
End Sub
Whenever you select a company from combo box 1, this is supposed to fill
combo box 2 with all of the selected company's employees. 100% of the
time,
the number of the selected client (ClientNum) WILL be found on the
"Employees" sheet. But every time I run it, it gets to the
WorksheetFunction.Match line and gives me this: "Application-defined or
object-defined error".
As I mentioned, I'm really wanting to know why this doesn't work, so I can
avoid doing it in the future. But any code corrections are also
appreciated.
Thanks.
|