Give array values

[shapper]

Hello,

I created an array of string as follows:

Dim MyData(1 To 6, 1 To 6) As String

Then I gave values to the 36 array elements as follows:
MyData(1,1) = "New York"
MyData(1,2) = "France"
MyData(1,3) = "John"
....
MyData(6,6) = "Radio"

Is there a way to fill the 36 elements instead of using one code line
for each array element?

Thanks,
Miguel

 

[Gary''s Student]

Here is a 2X5 example that is a little shorter:

Sub redistribute()
s = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
Dim MyData(1 To 2, 1 To 5) As Integer
k = 0
For i = 1 To 2
For j = 1 To 5
MyData(i, j) = s(k)
k = k + 1
Next
Next
End Sub

But it requires code to execute rather than just Dim'ing.

 

[Bob Phillips]

Dim MyData

MyData = [{"New
York","Radio",3,4,5,6;"France","TV",3,4,5,6;"John","Stereo",3,4,5,6;"London","MP3",3,4,5,6;"Germany","iPod",3,4,5,6;"Pete","All",3,4,5,6}]

This only works for a limited set of data

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

You can use a structure like this to load up your array...

Const NumberOfRows As Long = 6
Const NumberOfCols As Long = 6
Dim ColNum As Long
Dim RowNum As Long
Dim Temp() As String
Dim MyData(1 To NumberOfRows, 1 To NumberOfCols) As String
Temp = Split("New York,France,John,..etc...,Radio", ",")
For ColNum = 1 To NumberOfCols
For RowNum = 1 To NumberOfRows
MyData(RowNum, ColNum) = Temp(NumberOfCols * (RowNum - 1) + ColNum - 1)
Next
Next
Erase Temp

This works as long both dimensions of the MyData array start with 1....
simply assign the number of rows and columns to the appropriate Const
statements and put your list of items (across each row, column-by-column
ordering), comma delimited, into the first argument of the Split function
(do not try to pretty things up by placing spaces around the delimiter; and,
if any of your items have internal commas, you will need to use a different
delimiter, one that is not included within any items in the list, and change
the second argument of the Split function to reflect what it is).

Rick

 

[Alan Beban]

Or, if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

Temp = Split("New York, France, John, etc, Radio", ",")
arr = ArrayReshape(Temp, 6, 6)

Alan Beban