Problems with WorksheetFunction.Match
The more I work with it, the more it appears the problem starts at the
"AllClients =" line. There's something about the way I typed the range that
it doesn't like. I even tried getting rid of that line and just putting the
range directly into Match, but it didn't like that either.
Oh, and sorry about the way the code looks. I copied and pasted it directly
from the editor and that's how it came out.
"Luke" wrote:
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.
|