Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection Variable
Hey guys
I have a collection variable called "TestCollection" How do I specify to dump the entire collection into range A:A? Lets say there are 3 items in the collection... First item is dog, second is cat, third is bird. I want cell A1 to say dog, cell A2 to say cat, and cell A3 to say bird. How do I do this? Thank you Todd Huttenstine |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection Variable
Loop through the collection. to the best of my knowledge, there is no
support for dumping it in one command like you can do with an array. -- Regards, Tom Ogilvy "Todd Huttenstine" wrote in message ... Hey guys I have a collection variable called "TestCollection" How do I specify to dump the entire collection into range A:A? Lets say there are 3 items in the collection... First item is dog, second is cat, third is bird. I want cell A1 to say dog, cell A2 to say cat, and cell A3 to say bird. How do I do this? Thank you Todd Huttenstine |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection Variable
Does this count?
Sub Main() Dim col As Collection Set col = New Collection col.Add "dog", "dog" col.Add "cat", "cat" col.Add "bird", "bird" Range("A1:A" & col.Count) = _ WorksheetFunction.Transpose( _ Array( _ col.Item("dog"), _ col.Item("cat"), _ col.Item("bird"))) End Sub "Todd Huttenstine" wrote in message ... Hey guys I have a collection variable called "TestCollection" How do I specify to dump the entire collection into range A:A? Lets say there are 3 items in the collection... First item is dog, second is cat, third is bird. I want cell A1 to say dog, cell A2 to say cat, and cell A3 to say bird. How do I do this? Thank you Todd Huttenstine |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection Variable
For a collection of arbitrary length, you do have to loop, but at least you
can assign the range all at once. Sub Main() Dim col As Collection Set col = New Collection col.Add "dog", "dog" col.Add "cat", "cat" col.Add "bird", "bird" Dim v, str As String For Each v In col str = str & v & "," Next v = Split(str, ",") If UBound(v) -1 Then _ Range("A1:A" & col.Count) = _ WorksheetFunction.Transpose(v) End Sub "Todd Huttenstine" wrote in message ... Hey guys I have a collection variable called "TestCollection" How do I specify to dump the entire collection into range A:A? Lets say there are 3 items in the collection... First item is dog, second is cat, third is bird. I want cell A1 to say dog, cell A2 to say cat, and cell A3 to say bird. How do I do this? Thank you Todd Huttenstine |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection Variable
Try the dictionary object - its part of Microsoft Scripting Runtime - set a
reference to this from the Tools/References menu. An advantage of the Dictionary is that you can load the keys as well as the items into a variant & dump to a sheet. The following code should be in a standard module and the reference set. Option Explicit Private mDictionary As Scripting.Dictionary Sub Main() Range("B:C").Clear LoadDictioanry DumpDictionary End Sub Private Sub LoadDictioanry() Dim index As Long Dim key As String Set mDictionary = New Scripting.Dictionary For index = 1 To 50 + Int(Rnd() * 50) key = "" Do While Len(key) < (2 + Int(Rnd() * 5)) key = key & Chr(65 + Int(26 * Rnd)) Loop If Not mDictionary.Exists(key) Then mDictionary.Add key, Rnd() * 1000 End If Next End Sub Private Sub DumpDictionary() Dim keys Dim items keys = mDictionary.keys Range(Range("B2"), Cells(mDictionary.Count + 1, 2)) = _ Application.WorksheetFunction.Transpose(keys) Range(Range("C2"), Cells(mDictionary.Count + 1, 3)) = _ Application.WorksheetFunction.Transpose(mDictionar y.items) End Sub For the answer to the question, examine the DumpDictionary procedure. Run Main for the demo. -- Patrick Molloy Microsoft Excel MVP --------------------------------- I Feel Great! --------------------------------- "Todd Huttenstine" wrote in message ... Hey guys I have a collection variable called "TestCollection" How do I specify to dump the entire collection into range A:A? Lets say there are 3 items in the collection... First item is dog, second is cat, third is bird. I want cell A1 to say dog, cell A2 to say cat, and cell A3 to say bird. How do I do this? Thank you Todd Huttenstine |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Names Collection | Excel Programming | |||
Is a Collection the best option? | Excel Programming | |||
Is a Collection the best option? | Excel Programming | |||
Is a Collection the best option? | Excel Programming | |||
Sum textboxes in a collection | Excel Programming |