Ok. I have got my program running to where it will take the data in the array
and based on that data take more data out of access and paste it into various
Excel worksheets. I have hard coded what each cell in the array is so that
vba can do this. My problem is I am going to have arrays of various size. One
might be 200 lines and another might be 3 lines of data. Is there anyway to
program vba to figure out that the array is different and instead of looping
through 5 lines of data it will loop through 200? Here is the code I have for
defining the array. Thanks for any help. I know these questions on arrays are
getting old.


Option Explicit

Private mcnToDatabase As Connection
Private mwksResults As Excel.Worksheet


Private Const STATE_FIPS_COL = 0
Private Const COMMODITY_COLUMN = 1
Private Const PRACTICE_COL = 2
Public Const dbpath = "D:\Profiles\cherring\Desktop\New
Folder\DevelopIndexOut_no_Price_vol.mdb"



Private Const CS = "Provider=Microsoft.Jet.OLEDB.4.0;User
ID=Admin;Mode=Share Deny None;Jet OLEDB:Engine Type=4;Data Source="

Private Const CLIENT_TAB = "CLIENT"
Private Const ALT_TAB = "ALT1"

Sub MainMacro1()






Call Run(dbpath)


End Sub
Public Sub Run(dbpath As String)

Dim lDataRow As Long
Dim lData As Long
Dim alData() As Long

'Set asData = info in Excel

alData = GetAllData




For lDataRow = 0 To UBound(alData, 1) - 1
Main dbpath, alData(lDataRow, STATE_FIPS_COL), alData(lData,
COMMODITY_COLUMN), alData(lData, PRACTICE_COL)

'RunSolver
'Save as new workbook
'Next lDataRow
Next lDataRow
End Sub

Private Function GetAllData() As Long()
Dim alTemp() As Long

ReDim alTemp(2, 3)

Set mwksResults = Application.Worksheets("mwksResults")

With mwksResults
mwksResults.Activate

alTemp(0, 0) = .Range("A2").Value
alTemp(0, 1) = .Range("B2").Value
alTemp(0, 2) = .Range("C2").Value
alTemp(1, 0) = .Range("A3").Value
alTemp(1, 1) = .Range("B3").Value
alTemp(1, 2) = .Range("C3").Value
End With

GetAllData = alTemp

Erase alTemp
End Function

Private Function GetAllData() As Long()
Dim alTemp() As Long
Dim iCol As Long
Dim iEnd As Long
Dim iRow As Long

Set mwksResults = Application.Worksheets("mwksResults")
iEnd = mwksResults.Range("A2").End(xlDown).Row
ReDim alTemp(iEnd - 2, 2)
For iRow = 2 To iEnd
For iCol = 1 To 3
alTemp(iRow - 2, iCol - 1) = mwksResults.Cells(iRow,
iCol).Value
Next iCol
Next iRow

GetAllData = alTemp

Erase alTemp
End Function

Hth,
Merjet

Thank you merjet! It worked. You rock.

"merjet" wrote:

> Private Function GetAllData() As Long()
> Dim alTemp() As Long
> Dim iCol As Long
> Dim iEnd As Long
> Dim iRow As Long
>
> Set mwksResults = Application.Worksheets("mwksResults")
> iEnd = mwksResults.Range("A2").End(xlDown).Row
> ReDim alTemp(iEnd - 2, 2)
> For iRow = 2 To iEnd
> For iCol = 1 To 3
> alTemp(iRow - 2, iCol - 1) = mwksResults.Cells(iRow,
> iCol).Value
> Next iCol
> Next iRow
>
> GetAllData = alTemp
>
> Erase alTemp
> End Function
>
> Hth,
> Merjet
>
>
>