Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with WorksheetFunction.Match
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with WorksheetFunction.Match
Should be:
Set AllClients = Worksheets("Employees").Range(Cells(3, 1), Cells(LastEERow, 1)) Otherwise you get a value instead of the range object. "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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with WorksheetFunction.Match
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. "Luke" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with WorksheetFunction.Match
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 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. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with WorksheetFunction.Match
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with WorksheetFunction.Match
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. "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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with WorksheetFunction.Match
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with WorksheetFunction.Match
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with WorksheetFunction.Match
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheetfunction.match | Excel Programming | |||
Problems with WorksheetFunction.Index and WorksheetFunction.Match | Excel Programming | |||
Problems with application.worksheetfunction.correl | Excel Programming | |||
worksheetfunction.match | Excel Programming | |||
Worksheetfunction MATCH | Excel Programming |