Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reset Collection | Excel Programming | |||
Reset Collection | Excel Programming | |||
Collection Key | Excel Programming | |||
Collection | Excel Programming | |||
Reset New Collection | Excel Programming |