Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is a Collection the best option?
You've had great responses so far. I use the Scripting
Dictionary most of the time. A dictionary is basically a collection and works similarly to a collection. However, there are some very good methods available - for example the Rxists method allows you to test if a key exists... If NOT MyDic.Exists(ThisKey) then MyDic.Add Item, ThisKey End If One can loop through both the items AND the Keys which is really useful. As an alternative, I also use disconnected recordsets. These are powerful tools to use in code, as they allow sorting, filetering etc of data exactly as if one had used a SQL statement. In the following example, I have a column of data in a sheet starting in the third row of column "D". I've created a recordset to extract the unique items, and used the recordset's Sort method to drop the list of uique data into the sheet. Sub GetValues() Dim rw As Long ' loop index Dim sText As String ' cell value Dim rst As New ADODB.Recordset ' create & open the recordset With rst .Fields.Append "KeyName", adChar, 20 .Open End With 'initialise the row index rw = 3 'loop for each cell in the column Do Until Cells(rw, "D") = "" ' get the cell's value sText = Cells(rw, "D").Value ' check if we already have it in the recordset If Not rst.BOF Then rst.MoveFirst rst.Find "[KeyName]='" & sText & "'" ', _ , adSearchForward, True If rst.EOF Then ' no, then add it rst.AddNew 0, sText End If ' increent index for then next cell rw = rw + 1 Loop ' now sort the recordset rst.Sort = "KeyName ASC" 'rst.MoveFirst Range("A1").CopyFromRecordset rst rst.Close Set rst = Nothing End Sub The project requires a reference to the Active Data Objects 2.7 Library HTH Patrick Molloy Microsoft excel MVP -----Original 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 . |
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) | |||
Reformat IF(ISERROR(....) : if 1st option returns empty, look at 2nd option. | Excel Worksheet Functions | |||
Is a Collection the best option? | Excel Programming | |||
Is a Collection the best option? | Excel Programming |