Read a named range into an array

[brucemc]

I want to take a named range which is in a row consisting of month and year headings, such as "Aug 2007" and read them into an array to be used in a list box. I also want to store the column of each (such as "August 2007" is in column G, "September 2007" is in column F, and so on descending), so I suspect my best way is a two dimensional array. The column value will later be used to refer to all the corresponding fields in that column. I am using a named range because every month it grows by one item, the last month and year that we recorded. The listbox will be in one sheet, the data including the headings are together in another, both in the same workbook. I suspect my best place to put the code is in the WorkSheet:Activate event of the sheet where the listbox is placed.

I started to hack away at this and found myself dealing with too many variables in how to handle; I know that given enough time I will eventually get it but I am asking for help because I would probably waste a week or more when you folks probably know this stuff by heart. If someone could show me how to do this it is very much appreciated.

Thank-you!

[rylo]

Hi

You probably don't need to get the column reference, as there are easy ways to find the position once you have made a selection.

Bit hard without an example file to know your structure, but if you have a range called mths then the code below may help

Please Login or Register  to view this content.

If you need more guidance, then build an example file, put it up onto the post, and you will probably get more detailed code to suit your situation.


HTH

rylo

[brucemc]

You packed several things new to me in that short space; I am looking forward to experimenting with them!

I am still tooling with lengthy for/next structures with offsets as I could not recall the relationship between a named range and an array, which gives you a rough idea how far away I was from what you are showing me...

[Greg M]

Hi Bruce,

Try the following code:

Please Login or Register  to view this content.

where "MyDataRange" is the named range on "Sheet1" from which you want to populate the array. The reason for the two "-1" terms is that array dimensions are zero-based by default. You can use "Option base 1" at the module level if you'd prefer one-based dimensions for arrays, but just remember that it applies to ALL arrays defined in that module.

Just for information, if you want to populate a RANGE from an ARRAY, it's a bit simpler, i.e.

Please Login or Register  to view this content.

BUT, and it's a BIG "but", you must make sure that the dimensions of the range receiving the data correspond exactly to the dimensions of the array from which the data are taken.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[brucemc]

It gets thicker...

I want to learn the methods of rylo, but as I quickly understood yours, Greg M, I did the following...

Please Login or Register  to view this content.

Instead of a listbox I chose a combobox because it gave me a single choice in the box in it's "unactivated" state, and when the arrow was clicked it gave me a drop-down selection. I was unable to do that with the list box. Also something I can't understand is that I tell the cb (as was the case in the lb) there are two columns, I must bind column 1, but the month data appears to be in the second column for if I set that one to a 0 width ("31.95 pts; 0 pts" instead of "0 pts; 31.95 pts"), no more month. I am probably struggling with learning the concept of a two-dimensional array, I suspect, and the model I have in my head just ain't quite right.

Anyway, those are the curiosities to me. The real question is now that someone selects a month in the display, how do I determine at what position in the 2-D array it is at so I can extract the column?

I can always revert to doing a .Find in the range, but this is as much of a learning exercise to understand arrays better. Then I get to tackle some of the "rylo concepts"...

btw, the offsets, as you probably quickly figure out, are simply to provide the proper offset to read in the horizontal data.

I have been toying with the change event of the cb, but am having a difficult time locating anything that returns my place in the array. And I have an awful feeling in my head that both of you are laughing at how complicated I may have made this for myself, but hey, btdt (been there, done that) ...

As always, great appreciation.
-Bruce

[rylo]

Bruce

Have a look at the combobox listindex property. If you make a selection from the combobox, then the listindex will give the position. As you have based your array at 0, and the listindex is also based at 0, then the listindex will be the position in the array.

So something like

Please Login or Register  to view this content.

should show you the column that relates to the selection.

HTH

rylo