collection keys

[kippers2]

have a collection consisting of column numbers with the Weekno/year as its key

Is the a way of listing the keys of a collection?

[lucas]

Is this an Access question.....
If it's an excel question, keys are not used in excel as they are in Access but if your Weekno/year is in a column then that can be returned in a listobox....

[mikerickson]

You could create another collection, but if the Weekno/year is readable from the column that would probably be an easier way to find the key of for a column.

[kippers2]

To be clearer
In excel
I have a mass of data which has the date in the headings and the qty in the rows

I create a collection by reading across the heading and create a unique list of week numbers i.e. i convert every date to its week number and then try and add to the collection.
Thus i get a collection of column numbers with keys that are a unique list of week numbers

I then read through the data and every time i hit a value i find the date at the top of the column and find which column to add this number to in the collection by looking up the key.

My problem is i would like to list out my keys to show which week numbers i ended up getting (and thus is there any weeks missing)

[mikerickson]

It sounds like the keys to your collection and column headers store the same information.

It would be easier to create the list of week numbers from your headers than from your collection keys. If you are doing this to verify the thouroughness of the collection, what I would do is
from the headers, make an array of week numbers.
Loop through that array trying to add 1 to the collection with keys from the array. If every attempt results in an error, and UBound(array)=Collection.Count then you know that the Array is a complete list of Collection keys.

This is getting awfuly complicated, I am mostly suggesting that the information that you want to get from the set of keys to your Collection is more easily avaliable from your column headings.

[Jan Karel Pieterse]

I agree with mikerickson that doing this by using a collection is overly complex.

Here is what I would do if I wanted to avoid VBA:

- transpose your data, so the dates and their values are in columns
- add a column with a cell formula calculating the weeknumber from the date
- Use a pivot table to sum up the numbers by week number.
- done.

[kippers2]

think i know what you mean will give it a go tonight

Good help though - made me think about it from a different angle