I have a collection (2D, 1 column to make it simple). Can I directly write it to a range or I need to convert it to an array to do that? Many thanks!
I have a collection (2D, 1 column to make it simple). Can I directly write it to a range or I need to convert it to an array to do that? Many thanks!
A collection cannot be written directly to a work sheet.
This code will take the elements of a collection and write them to a sheet. (Usually a collection holds objects, not values.)
I'm curious about your collection. Frequently, collections hold objects, not variables, so writing them to a sheet involves a property of the items in the collection, not the item itself.Dim i As Long Dim myArray as Variant With Sheet1.Range("A1").Resize(myCollection.Count, 1) Redim myArray(1 to .Rows.Count, 1 to .Columns.Count) For i = 1 to myCollection.Count myArray(i, 1) = myCollection(i) Next i .Value = myArray End With
Also, there is no such thing as a 2D collection. All collections are one dimensional.
Last edited by mikerickson; 01-09-2016 at 09:02 PM.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Thanks Mike! I use the collection to get unique values for a combo box items. Good to know there is only one dimension in collections. I can only deal with one column
However I always have to redim preserve and then transpose the array to get the values, even for 1 column:
ReDim Preserve myArray(1 To myCollection.Count) For i = 1 To myCollection.count myArray(i) = myCollection.Item(i) Next m sheet1.range("myRange")=application.worksheetfunction.transpose(myArray)
Last edited by aprildu; 01-11-2016 at 12:14 AM.
Perhaps you'd be interested in these codes. Neither uses a collection.Thanks Mike! I use the collection to get unique values for a combo box items.
Rem put items in combobox, alphabetical order, no duplicates Dim oneItem As String Dim oneCell As Range Dim i As Long For Each oneCell In Range("A1:A7") oneItem = WorksheetFunction.Proper(CStr(oneCell.Value)) For i = 0 To ComboBox1.ListCount - 1 If oneItem < ComboBox1.List(i) Then Exit For ElseIf oneItem = ComboBox1.List(i) Then GoTo NextLoop End If Next i ComboBox1.AddItem oneItem, i NextLoop: Next oneCell
Rem put combobox list in a range Dim myList As Variant myList = ComboBox1.List Range("Q1").Resize(UBound(myList) + 1, 1).Value = myList
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks