Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is a Collection the best option?
I could be wrong, but I think that collections are groups of variables or
objects, not values. (I have made collections of form controls to simulate control arrays in VB6 in Excel VBA.) What you may need is a dynamic array. Declare it with empty parentheses and redim it when you add an item. Use the keyword Preserve to avoid erasing the data in the array. That works well for 1 dimensional arrays, but if you need a two or more column array, it gets kinda hairy. In your code, substitute Dim EnquiryList() as Variant in the declarations. Then sub ReDim Preserve EnquiryList(1 to Ubound(EnquiryList)+1) 'expand array size by one EnquiryList(Ubound(EnquiryList)) = Cell.Value 'put unique value in array for EnquiryList.Add Cell.Value If you just want to add these unique values to a list box in a form, use the additem method and don't bother with the array. ListBox1.AddItem Cell.Value Hope this helps. "Stuart" wrote in message ... I'm looking to take unique values from a defined range into a form. The values will be alphabetic characters. Here is what I have so far: Sub SortContractorsSuppliers() Dim ws As Worksheet, LastRow As Long Dim DataRange As Range, Cell As Range Dim EnquiryList As New Collection For Each ws In ActiveWorkbook.Worksheets With ws .Unprotect LastRow = Application.Max(.Range("H65536") _ .End(xlUp).Row, .Range("I65536").End(xlUp).Row, _ .Range("J65536").End(xlUp).Row) Set DataRange = Range("H2", "J" & LastRow) On Error Resume Next For Each Cell In DataRange If Not IsEmpty(Cell) Then EnquiryList.Add Cell.Value End If Next Cell On Error GoTo 0 End With Next MsgBox EnquiryList End Sub Why is it that when the code loops into the 2nd sheet, any unique values in that sheet's DataRange are not added to the Collection, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is a Collection the best option?
Ugh. Looks like my comments got wrapped in that code.
Use this: Dim EnquiryList() as Variant ReDim Preserve EnquiryList(1 to Ubound(EnquiryList)+1) EnquiryList(Ubound(EnquiryList)) = Cell.Value The comments aren't necessary. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dont have secondary axis option listed in Series Option | Charts and Charting in Excel | |||
preserve formatting option in pivot table option dialog box | Excel Discussion (Misc queries) | |||
keep source formatting is not an option in paste option button | Excel Discussion (Misc queries) | |||
Reformat IF(ISERROR(....) : if 1st option returns empty, look at 2nd option. | Excel Worksheet Functions | |||
Sum textboxes in a collection | Excel Programming |