Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
type mismatch error when passing collection byRef
I have two possible collections that I might want a particular sub to operate
on. Both are Dim'd at the top of the module: Dim my_AEs, my_AMs As Collection .... the sub in question is: Sub identify_team(ByVal team_name As String, ByRef col As Collection) Dim last_row As Integer Dim team_range As Range Dim myRow As Range Set col = New Collection last_row = Sheets("Sales Team Lookup").Range("A1").End(xlDown).Row Sheets("Sales Team Lookup").Activate '**can't avoid this line Set team_range = Sheets("Sales Team Lookup").Range(("A1:E1"), Range("A:E").Rows(last_row)) For Each myRow In team_range.Rows If myRow.Cells(1, 2).Value = team_name And myRow.Cells(1, 4) = "AE" Then col.Add Item:=myRow.Cells(1, 1).Value, Key:=myRow.Cells(1, 1).Value End If Next myRow End Sub I call this by: identify_team my_team_name, my_AEs but this gives me a type mismatch error and highlights my_AEs. The watch window says at this point that the Type of my_AEs is Variant/Object/Collection. I'd appreciate any advice or insights on this. (I'd also really like to know why I need the line Sheets("Sales Team Lookup").Activate in identify_team. If I leave this out it stops on the next line. I can then click on the Sales Team Lookup sheet and click on continue and it runs the rest of it fine. I don't understand why this is) Many thanks in advance. Tara H |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
type mismatch error when passing collection byRef
Dim my_AEs, my_AMs As Collection
The above line declares my_AEs as a Variant (since it's unspecified) and my_AMs as a Collection. My guess is that since my_AEs isn't a collection that's why you get the error when you pass it as one. try: Dim my_AEs As Collection, my_AMs As Collection or (my preference, for readability if no other reason): Dim my_AEs As Collection Dim my_AMs As Collection HTH, "Tara H" wrote in message ... I have two possible collections that I might want a particular sub to operate on. Both are Dim'd at the top of the module: Dim my_AEs, my_AMs As Collection ... the sub in question is: Sub identify_team(ByVal team_name As String, ByRef col As Collection) Dim last_row As Integer Dim team_range As Range Dim myRow As Range Set col = New Collection last_row = Sheets("Sales Team Lookup").Range("A1").End(xlDown).Row Sheets("Sales Team Lookup").Activate '**can't avoid this line Set team_range = Sheets("Sales Team Lookup").Range(("A1:E1"), Range("A:E").Rows(last_row)) For Each myRow In team_range.Rows If myRow.Cells(1, 2).Value = team_name And myRow.Cells(1, 4) = "AE" Then col.Add Item:=myRow.Cells(1, 1).Value, Key:=myRow.Cells(1, 1).Value End If Next myRow End Sub I call this by: identify_team my_team_name, my_AEs but this gives me a type mismatch error and highlights my_AEs. The watch window says at this point that the Type of my_AEs is Variant/Object/Collection. I'd appreciate any advice or insights on this. (I'd also really like to know why I need the line Sheets("Sales Team Lookup").Activate in identify_team. If I leave this out it stops on the next line. I can then click on the Sales Team Lookup sheet and click on continue and it runs the rest of it fine. I don't understand why this is) Many thanks in advance. Tara H |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
type mismatch error when passing collection byRef
*slaps forehead* Thank you!
I guess what was confusing me was that it was showing as Variant/Object/Collection. I also tried Set my_AEs = new collection at one point and that didn't seem to help. I guess that explains a couple of other mysterious errors I've been having... Thanks! Tara H "George Nicholson" wrote: Dim my_AEs, my_AMs As Collection The above line declares my_AEs as a Variant (since it's unspecified) and my_AMs as a Collection. My guess is that since my_AEs isn't a collection that's why you get the error when you pass it as one. try: Dim my_AEs As Collection, my_AMs As Collection or (my preference, for readability if no other reason): Dim my_AEs As Collection Dim my_AMs As Collection HTH, "Tara H" wrote in message ... I have two possible collections that I might want a particular sub to operate on. Both are Dim'd at the top of the module: Dim my_AEs, my_AMs As Collection ... the sub in question is: Sub identify_team(ByVal team_name As String, ByRef col As Collection) Dim last_row As Integer Dim team_range As Range Dim myRow As Range Set col = New Collection last_row = Sheets("Sales Team Lookup").Range("A1").End(xlDown).Row Sheets("Sales Team Lookup").Activate '**can't avoid this line Set team_range = Sheets("Sales Team Lookup").Range(("A1:E1"), Range("A:E").Rows(last_row)) For Each myRow In team_range.Rows If myRow.Cells(1, 2).Value = team_name And myRow.Cells(1, 4) = "AE" Then col.Add Item:=myRow.Cells(1, 1).Value, Key:=myRow.Cells(1, 1).Value End If Next myRow End Sub I call this by: identify_team my_team_name, my_AEs but this gives me a type mismatch error and highlights my_AEs. The watch window says at this point that the Type of my_AEs is Variant/Object/Collection. I'd appreciate any advice or insights on this. (I'd also really like to know why I need the line Sheets("Sales Team Lookup").Activate in identify_team. If I leave this out it stops on the next line. I can then click on the Sales Team Lookup sheet and click on continue and it runs the rest of it fine. I don't understand why this is) Many thanks in advance. Tara H |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP "ByRef Argument Type Mismatch" | Excel Programming | |||
passing range to function: type of argument is byref incompatible | Excel Programming | |||
Passing Userform Listbox to sub causes type-mismatch error | Excel Programming | |||
"ByRef argument type mismatch" Error | Excel Programming | |||
ByRef argument type mismatch error? | Excel Programming |