Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array problem
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array problem, I think.. | Excel Worksheet Functions | |||
Array problem - TIA | Excel Worksheet Functions | |||
Array Problem | Excel Discussion (Misc queries) | |||
Array Problem?? | Excel Programming | |||
array use problem | Excel Programming |