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

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

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