Beautiful. That did exactly what I needed. I'm in the process of converting
a massive, time-consuming workbook into a smaller, more efficient one using
VBA instead of the numerous and lengthy Excel formulas currently in use. I
knew I had to get past this because I have several more instances where I'm
going to need to use code very similar to this.

Thanks for the help and insight.

"Patrick Molloy" wrote:

this line will raise issues for you since cells refers tot he active sheet,
not necessarily Employoyees. A fix is to use the WITH statement

TotalEEs = Application.WorksheetFunction.CountIf(Worksheets
_("Employees").Range(Cells(3, 1), Cells(LastEERow, 1)), ClientNum)

WITH Worksheets("Employees")
TotalEEs = Application.WorksheetFunction.CountIf(.Range(.Cell s(3,
1), .Cells(LastEERow, 1)), ClientNum)
END WITH
!!!note the . before cells, so they're also part of the WITH worksheet

the same is true for AllClients

WITH Worksheets("Employees")
AllClients = .Range(.Cells(3, 1), .Cells(LastEERow, 1))
END WITH







"Luke" wrote:

I just changed the Range from dynamic (using Cells) to static ("A3:A27745")
and it took it, but I need it to be dynamic as the number of rows will change
monthly. However, once it got past the Range problem, it hit another one at
the "TotalEEs = " line, so I've got that to look forward to as well.

"Patrick Molloy" wrote:

teh MATCH function raises an error when no match is found, I prefer to wrap
it in my own to trap the error, others prefer ON ERROR RESUME NEXT


AllClients = Worksheets("Employees").Range(Cells(3, 1), Cells(LastEERow, 1))
should be
SET AllClients = Worksheets("Employees").Range(Cells(3, 1),
Cells(LastEERow, 1))

have you stepped to this line and evaluated the various varaibles, eg what
does ClientNum contain? select the sheet Employees then in the immedaite
window put Allclients.select to ensure that the range is good.

make sure you have
OPTION EXPLICIT at the start of the module


"Luke" wrote:

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.