Problems with WorksheetFunction.Match

[Luke]

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.

 

[JLGWhiz]

Does cell P1 actually contain an integer? I assumed that it did or you
would have gotten an error earlier in the code than you did. Not being able
to see the sheet forces me to assume that your variables match the data
types that were delcared. But the object variable for AllClients was not
properly Set, leaving it to be interpreted by the compiler as a variant
variable. But if it still balked after using the Set key word then that
leaves the other variable , LastEERow, in question. One way to find the
problem is to step through the procedure using function key F8 and use the
tool tips display to check the value of the variables at each step to make
sure they are what they should be. Just mouse over the variable after a
line executes and the tool tip should show the value. You might have to use
a message box to capture the value of ObjectVariables that are created using
the Set key word, but all other variables should show up in the tool tips.
Also double check the spelling.
Other than the missing Set key word previously noted, I don't see why it
would not work if the variables are the values that they should be.

 

[Dave Peterson]

You have a couple of problems.

In this line:
AllClients = Worksheets("Employees").Range(Cells(3, 1), Cells(LastEERow, 1))

You need Set (since you're working with objects (a Range). And those
unqualified Cells() will refer to the activesheet--not always the Employees
sheet!

I'd use:
With worksheets("employees")
set AllClients = .Range("A3", .cells(LastEERow, 1))
end with
(.cells(3,1) is the same as A3--but A3 is easier to read (for me, anyway).)

And...

If there is no match in the =match() formula, then the
application.worksheetfunction.match() will cause a runtime error.

You can use this instead:

Dim ClientStart as Variant 'not long, since it could be an error.
.....
ClientStart = Application.Match(ClientNum, AllClients, 0)
'Using application.match will return an error that you can test.

if iserror(clientstart) then
'no match, what should happen?
else
'....

========
This is untested and uncompiled.

Option Explicit
Private Sub ComboBox1_Change()

Dim MaxEEs As Long
Dim LastEERow As Long
Dim TotalEEs As Long
Dim ClientStart As Variant
Dim ClientNum As Long 'don't bother using "As Integer"'s
Dim EEList As Long
Dim AllClients As Range

MaxEEs = Worksheets("Employees").Range("P1").Value

LastEERow = MaxEEs + 2

'The Me keyword refers to the object owning the code
'in this case, it's the userform with the Combobox
'being clicked.
'(Don't use the userform's name. It'll just be more to fix
'if you change names or copy to a different userform.)
With Me.ComboBox2
.AddItem "All Employees" 'no need for the surrounding ()'s
If Me.ComboBox1.ListIndex = 0 Then
TotalEEs = MaxEEs
ClientStart = 1 'why is this here? It's overwritten later.
End If

ClientNum = Val(Left(Me.ComboBox1.Value, 4))

With Worksheets("Employees")
'you need to use the Set command when you're working
'with objects like Ranges.
Set AllClients = .Range("A3", .Cells(LastEERow, 1))
'the leading dots means that those objects/methods belong to the
'object in the preceding With statement.
'In this case, Worksheets("Employees")
End With

ClientStart = Application.Match(ClientNum, AllClients, 0)
If IsError(ClientStart) Then
'what should happen here?
'msgbox or some label shows an error message???
'maybe that's why you had
ClientStart = 1 'previously????
Else
'use the variable AllClients instead of (Fixing) that
'long expression
TotalEEs _
= Application.WorksheetFunction.CountIf(allclients, ClientNum)
end if

'but make sure clientstart is a number--even if there is no match--if
'you want to use this statement.
For EEList = ClientStart To TotalEEs
.AddItem Worksheets("Employees").Range("A2").Offset(EEList, 2)
Next EEList
'I wasn't sure if this should be part of the previous Else statement.
End With
End Sub

 

[Patrick Molloy]

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]

Hmm, I've done the things you suggest (stepping through, using "Set"), but
none of it seems to work. I've also verified that the specific ClientNum I
choose was in the defined range. As an aside, ClientNum will always be in
the range because the only possible values of ClientNum are those contained
in the AllClients range.

Just to make sure, though, Assuming LastEERow = 27000, then my AllClients
range should be A3:A27000 (based on the formula), right?

And Option Explicit is on. I don't trust my memory enough to work without it.

 

[Luke]

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]

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(.Cells(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]

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.