Multiple nested arrays to fill from a worksheet

[Dabo]

I modified everything and now ask a much simpler question :

how do I refer to a value contained in a nested array
for example

Array2 = Array1(1,1)
Array2(1,1) = "Hi"


I tried to reach "Hi" using Array1 but when I type Array1(1,1)(1,1) or Array1((1,1),(1,1)) I get a syntax error.

Does somebody knows the coordinates ?

Thanks !

[Bob Phillips]

Explain in English what your objectives are. Multi-dimensional arrays are probably the solution, but I don't get what you are doing.

[Dabo]

Ok,

I modified my question :)

[GTO]

I modified everything and now ask a much simpler question

Greetings Dabo,

I see you just recently joined and I wanted to say Welcome and a friendly howdy from Arizona. You will get to 'meet' some mighty nice folks here, who will go out of their way to assist in great solutions :-)

Now I hope you don't mind, but if I understand what you just did, you edited your first post after XLD responded. I don't believe that you'll get the best help this way, as imagine how confusing it is for any 'answerer' to have to scroll up and down and try and figure what post follows what!

If you just re-ask/modify the question in your next post, then we can all follow along in an orderly fashion and see what has been tried, what is working (or not) and what is being understood/communicated thus far. Make sense?

Now as to your question, copy the below to a Standard Module (in a blank/new wb), and step through it with the Locals window open. If you click the plus signs (expand) you'll be able to see what is happening.

Option Explicit

Sub ex()
Dim Array2
Dim Array1(1 To 3, 1 To 3)

'// You did not specify how many dimensions or elements per dimension there are, //
'// so for our example, I'll make it explicit as dimensioned above, and we'll use //
'// a lower bound of one, just so as this is easy to "imagine" or "picture" like //
'// rows and columns on a worksheet. //
'// Now by default, rows will will be the first dimension and columns the second. //
'// So, we'll just build our array like cell addresses. //
Array1(1, 1) = "A1"
Array1(1, 2) = "B1"
Array1(1, 3) = "C1"
Array1(2, 1) = "A2"
Array1(2, 2) = "B2"
Array1(2, 3) = "C2"
Array1(3, 1) = "A3"
Array1(3, 2) = "B3"
Array1(3, 3) = "C3"

'// Now when you do this, you are assigning the string value "A1" to Array2.
Array2 = Array1(1, 1)
'// But this will fail, as Array2 is now a single value (non array) variant/string //
'// variable. //
'Array2(1, 1) = "Hi"

'// You could however, assign the values of the elements in Array1() to another //
'// array if you dimensioned the Array2 as an array first. //
'// In this example, we'll change (re-dimension or ReDim) Array2 to a linear or //
'// single diemension array containing 9 elements, and using a base of one (zero is //
'// default. //
ReDim Array2(1 To 9)

Array2(1) = Array1(1, 1)
Array2(2) = Array1(1, 2)
Array2(3) = Array1(1, 3)
Array2(4) = Array1(2, 1)
Array2(5) = Array1(2, 2)
Array2(6) = Array1(2, 3)
Array2(7) = Array1(3, 1)
Array2(8) = Array1(3, 2)
Array2(9) = Array1(3, 3)

MsgBox Array2(1)

'// ...and of course we could access the coordinates as you asked like:
MsgBox Array1(1, 2)
MsgBox Array2(2)
End Sub


Well, Bob (XLD) and others could certainly explain more complex arrays far more succinctly, but hopefully this will help a bit. While you will want to read thru the help topics for Working With Arrays and Option Base, I certainly have learned more here about them than from the help file.

Have a great day,

Mark

[Dabo]

Dear GTO,

thank you so much for your advices and warm welcome !

Your solution works perfectly,
in addition you can actually access a "sub array" using this kind of coordinates :
Array(1,1)(1,1)

Thanks again