Hi there,

I've got a collection that I want to sort and put into a listbox. The
collection is a series of cell references (delimited with a comma) and each
item has a string key on which I want to sort.

So how can I run through my collection to pull out all the index keys?

Thanks in advance

Best regards

John

to the best of my knowledge, you can't retrieve the index keys.

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Hi there,

I've got a collection that I want to sort and put into a listbox. The
collection is a series of cell references (delimited with a comma) and
each
item has a string key on which I want to sort.

So how can I run through my collection to pull out all the index keys?

Thanks in advance

Best regards

John

Thanks Tom. In that case I'd better go back to where the collection gets
built add an duplicate array I suppose.

Thanks again

Best regards

John
"Tom Ogilvy" wrote in message
...
to the best of my knowledge, you can't retrieve the index keys.

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Hi there,

I've got a collection that I want to sort and put into a listbox. The
collection is a series of cell references (delimited with a comma) and
each
item has a string key on which I want to sort.

So how can I run through my collection to pull out all the index keys?

Thanks in advance

Best regards

John

in tools = references, set a reference to the Microsoft Scripting Runtime
library. this will give you access to the dictionary object which does have
a Key property. By setting the reference, you will be able to look at its
properties in the object browser.


here is sample code from MSDN. The use of createObject is an example of
late binding so you don't have to create the reference to use the
dictionary. My recommendation was to do it during development so you would
have access to the object browser.

Dim d ' Create a variable.
Set d = CreateObject("Scripting.Dictionary")
d.Add "a", "Athens" ' Add some keys and items.
d.Add "b", "Belgrade"
d.Add "c", "Cairo"


Check if it exists


If d.Exists("c") Then
msg = "Specified key exists."
Else
msg = "Specified key doesn't exist."
End If


Use items


a = d.Items ' Get the items.
For i = 0 To d.Count -1 ' Iterate the array.
s = s & a(i) & "<BR" ' Create return string.
Next


Use keys


a = d.Keys ' Get the keys.
For i = 0 To d.Count -1 ' Iterate the array.
s = s & a(i) & "<BR" ' Return results.
Next


--
Regards,
Tom Ogilvy

"John" wrote in message
...
Thanks Tom. In that case I'd better go back to where the collection gets
built add an duplicate array I suppose.

Thanks again

Best regards

John
"Tom Ogilvy" wrote in message
...
to the best of my knowledge, you can't retrieve the index keys.

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Hi there,

I've got a collection that I want to sort and put into a listbox. The
collection is a series of cell references (delimited with a comma) and
each
item has a string key on which I want to sort.

So how can I run through my collection to pull out all the index keys?

Thanks in advance

Best regards

John

Hi Tom,

This is great. I've not used the dictionary object before, so I'll have a
go.

Thanks very much for the help.

Best regards

John
"Tom Ogilvy" wrote in message
...
in tools = references, set a reference to the Microsoft Scripting Runtime
library. this will give you access to the dictionary object which does
have
a Key property. By setting the reference, you will be able to look at its
properties in the object browser.


here is sample code from MSDN. The use of createObject is an example of
late binding so you don't have to create the reference to use the
dictionary. My recommendation was to do it during development so you
would
have access to the object browser.

Dim d ' Create a variable.
Set d = CreateObject("Scripting.Dictionary")
d.Add "a", "Athens" ' Add some keys and items.
d.Add "b", "Belgrade"
d.Add "c", "Cairo"


Check if it exists


If d.Exists("c") Then
msg = "Specified key exists."
Else
msg = "Specified key doesn't exist."
End If


Use items


a = d.Items ' Get the items.
For i = 0 To d.Count -1 ' Iterate the array.
s = s & a(i) & "<BR" ' Create return string.
Next


Use keys


a = d.Keys ' Get the keys.
For i = 0 To d.Count -1 ' Iterate the array.
s = s & a(i) & "<BR" ' Return results.
Next


--
Regards,
Tom Ogilvy

"John" wrote in message
...
Thanks Tom. In that case I'd better go back to where the collection gets
built add an duplicate array I suppose.

Thanks again

Best regards

John
"Tom Ogilvy" wrote in message
...
to the best of my knowledge, you can't retrieve the index keys.

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Hi there,

I've got a collection that I want to sort and put into a listbox. The
collection is a series of cell references (delimited with a comma) and
each
item has a string key on which I want to sort.

So how can I run through my collection to pull out all the index keys?

Thanks in advance

Best regards

John