Reset Collection
 

Sub abc()
Dim col As Collection
For i = 1 To 10
' Set col = Nothing
Set col = New Collection
Max = Int(Rnd() * 15 + 1)
For j = 1 To Max
k = Int(Rnd() * (Max / 2) + 1)
On Error Resume Next
col.Add k, CStr(k)
On Error GoTo 0
Next
Debug.Print col.Count
Next
End Sub

worked with or without the commented out line. (xl2003)



--
Regards,
Tom Ogilvy


"Minitman" wrote:

Greetings,

Norman Jones helped me with a neat trick using a Collection to remove
duplicate entries from a range. I works great - for the first range -
after that it keeps giving a permission denied error. I think it
might have something to do with the method of resetting the
collection.

Anyone know the best way to load a ComboBox with the contents of a
collection and then reset the collection to fill the next ComboBox?

Any help is much appreciated.

TIA

-Minitman

Reset Collection
 

Hey Tom,

Thanks for the reply. It's good to hear from you again.

This is an interesting sub.

I wasn't aware of Debug.Print, and what all it does. After the
reference I checked the M$ help file for more info. I think I can use
that.

My code is similar to yours so I gave up and went to prepare a small
sample workbook to send. As I was down sizing my workbook (I started
with deleting a sheet and removing linked named ranges) I discovered
that most of my dynamic named ranges for the remaining page were
slightly corrupted. There was enough there for them to load into
variables and load items into a Collection, but not enough to come out
of the collection into the ComboBox. THAT was the problem. Once the
named ranges were corrected, the code now works fine.

Again, thank you.

-Minitman


On Thu, 27 Jul 2006 06:25:02 -0700, Tom Ogilvy
wrote:

Sub abc()
Dim col As Collection
For i = 1 To 10
' Set col = Nothing
Set col = New Collection
Max = Int(Rnd() * 15 + 1)
For j = 1 To Max
k = Int(Rnd() * (Max / 2) + 1)
On Error Resume Next
col.Add k, CStr(k)
On Error GoTo 0
Next
Debug.Print col.Count
Next
End Sub

worked with or without the commented out line. (xl2003)

Reset Collection
 

"Tom Ogilvy" skrev i en meddelelse
...
Sub abc()
Dim col As Collection
For i = 1 To 10
' Set col = Nothing
Set col = New Collection
Max = Int(Rnd() * 15 + 1)
For j = 1 To Max
k = Int(Rnd() * (Max / 2) + 1)
On Error Resume Next
col.Add k, CStr(k)
On Error GoTo 0
Next
Debug.Print col.Count
Next
End Sub

worked with or without the commented out line. (xl2003)



--
Regards,
Tom Ogilvy



Sub abc()
Dim col As New Collection
For i = 1 To 10
Set col = Nothing
' Set col = New Collection
Max = Int(Rnd() * 15 + 1)
For j = 1 To Max
k = Int(Rnd() * (Max / 2) + 1)
On Error Resume Next
col.Add k, CStr(k)
On Error GoTo 0
Next
Debug.Print col.Count
Next
End Sub

also works with or without the commented out line. (xl2003)

--
Best regards
Leo Heuser