object or array ?
The best way of doing this is with collections, as they won't allow
duplicates.
String the 3 fields together with a delimiter and add into the collection
like so
Dim coll As Collection
Dim cell As Range
Dim tmp As String
Set coll = New Collection
On Error Resume Next
For Each cell In Range("A1:A3")
tmp = cell.Value & "~" & cell.Offset(0, 1).Value & "~" &
cell.Offset(0, 2).Value
coll.Add tmp, tmp
Next cell
On Error GoTo 0
you can then split it up like so
Dim ary
Dim itm
For Each itm In coll
ary = Split(itm, "~")
MsgBox ary(0) & ", " & ary(1) & ", " & ary(2)
Next itm
--
---
HTH
Bob
(change the xxxx to gmail if mailing direct)
"--== Alain ==--" wrote in message
...
Hi,
I have a sheet in which i have several columns. for example, column A
contains "publisher name", column B contains "software name" and column C
contains "version"
i would like to scan all records of this sheet and store result in some
array, collection or objects...it depends what will be the most effective
one.
the process should be the following one :
for each record, application should search in the object or array if the
data already exist (i mean "publisher/software/version"), if it exists
therefore it will increase by 1 the relative value.
at the end i should be able to create an overview sheet like that :
Publisher Name Software Name Licenses used
Microsoft Excel 2003 5
Microsoft Word XP 2
Microsoft Outlook 2003 68
So what is the best : object ? Array or something else ?
thanks
Al.
|