I have an array which I want to assign an array of varabiles set by a
worksheet reference. Is this a multidimensional array?

Anyway myArr part of the code works fine but I cannot get myVar to.

In case I am thinking of this the wrong way this is what I am trying to
do......

myArr is an array of strings that I loop through to specify a worksheet.
The 2nd dimension is that each worksheet needs to be assigned a value from
another worksheet, however changes pending on the elemet in myArr. myVar
represents the secondary array.

To strat with I just want to return the value of myVar using MsgBox
(myVar(k))

Any ideas?

Bruce



Sub mySec()

Dim myVar(1 To 6)
Dim myArr
ReDim myArr(1 To 6)

pathA = ThisWorkbook.Path & "\ASX.mdb"
pathB = ThisWorkbook.Path

myArr = Array("Sec1", "Sec2", "Sec3", "Sec4", "Sec5", "Sec6")

myVar(1) = Array(Sheets("Shares").Range("D7"))
myVar(2) = Array(Sheets("Shares").Range("F7"))
myVar(3) = Array(Sheets("Shares").Range("H7"))
myVar(4) = Array(Sheets("Shares").Range("J7"))
myVar(5) = Array(Sheets("Shares").Range("L7"))
myVar(6) = Array(Sheets("Shares").Range("N7"))

k = 1

For Each a In myArr

MsgBox (myVar(k))

With Worksheets(a).Range("A1").QueryTable
.Connection = _
"ODBC;DSN=MS Access Database;DBQ=" & pathA & _
";DefaultDir=" & pathB & ";DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;"

.CommandText = Array( _
"SELECT Top 30 ImportDate, ASXCode, Volume, Open, High, Low,
Close" & Chr(13) & "" & Chr(10) & _
"FROM qry_ASX_Data" & Chr(13) & "" & Chr(10) & "", _
"WHERE (ASXCode='NAB')" & Chr(13) & "" & Chr(10) & _
"ORDER BY ImportDate DESC")

.FillAdjacentFormulas = True
.Refresh BackgroundQuery:=False
End With

k = k + 1

Next a

End Sub