Array problem

[Bruce]

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

 

[Sharad]

Hello,
1.
make it
myVar(1) = Sheets("Shares").Range("D7") isntead of using
Array(Sheets.....

myVar(1) is a single element of an array so you need not use Array(....)
to assign the value. If you do, you will not be able to use it in
MsgBox, because MsgBox can not display arrays, it will give error Type
Mismatch.

2. It should be MsgBox myVar(k) and not MsgBox (myVar(k)). (remove
extra parantheses.)

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!