refer part of a multi dimension array

[aprildu]

I have a 2 dimensional array: ar(1 to 2, 1 to 100). I need to refer to the first element of the first dimension ar(1, 1 to 100) without creating a new one. Is that possible? The reason I don't want to create a separate new array is because I need to use the value of the the second element of the first dimension ar(2, 1 to 100). For example something like:

Please Login or Register  to view this content.

Then I want to do something like:

Please Login or Register  to view this content.

But I don't think ar(1, 1 to 100) is recognized.

To give a better explanation, for example, I need to create an array of the worksheet name sorted with their corresponding indexes based on the array of randomized sheet name and their indexes. I know this may not be the best description of my problem and I know there are other ways to achieve this specific goal but I am wondering if an array can be used so I can use it for other purposes. Many thanks in advance!

[mike7952]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[mike7952]

Edit: OP changed requirements

[mike7952]

So you need the names of the worksheets sorted? What are you going to do with the array?

[aprildu]

This is just an example to show what I want. It's not about this specific task. It's about if I can get part of the array and use it the way I want.

[shg]

Please Login or Register  to view this content.

[Norie]

Perhaps you could use Index, here's a very simple example.

Please Login or Register  to view this content.

y will return a 1-dimensional array with the values from A1:D1.

z will return a 2-dimensional array with the values from A1:A15.

[bakerman2]

@ shg

I prefer to use Application instead of worksheetfunction because in case of error the Worksheetfunction goes into debug while the Application variant gives error on worksheet but code runs through.
Also with the Application variant you can easily trap the error using IsError and act accordingly.

Just a thought.

[aprildu]

@Shg @Norie @Bakerman. Thanks! However my problem doesn't involve any specific sheet or cells. Also I am trying to generate another array based on the first array but it will be sorted (for example). The problem is I want the second element of my original array to be attached to my newly created array. I tried the code but when I change it to av=[{1,2,3;a,b,c}], the index function throws a 1004 error.

My goal is to create another two dimensional array based on the first array - such as from [{1,3,2;a,b,c}] to [{1,2,3;a,c,b}]