Array Looping: I am almost done, can someone please help me???

[Guest]

I have an array in excel and an Access database. In the array there are three
columns. They are StateFip, CommodityCode and Practice Code. Based on these 3
values I want Excel to run my macro, which goes into Access, retrieves data
specific to these 3 values and pastes into the workbook and carries out
multiple calculations. When this is done I want it to save the file based on
3 cells and then go down to the next line in the array and repeat all of
these steps. With help from many posters on here I am so close. I will post
the code and then make some comments in the end.


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\Automateopt
updated!\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
Dim s As String
Dim r As String
Dim t As String

s = Replace(Worksheets("CountyYield").Range("A5").Text, " ", "")
r = Worksheets("CountyYield").Range("B5").Text
t = Worksheets("CountyYield").Range("C5").Text




'Set asData = info in Excel






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

'RunSolver
'Save as new workbook
'Next lDataRow
ActiveWorkbook.SaveAs Filename:="D:\Profiles\cherring\Desktop\states\" &
s & "_" & r & "_" & t & ".xls"

Next lDataRow
End Sub
Private Function GetCurrentData() 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

GetCurrentData = alTemp

Erase alTemp
End Function


Private Sub ConnectToDatabase(dbpath As String)


End Sub


Basically it starts out with Macro1() and then goes into the PublicSubRun.
When it does that I want it to go into the array and do what I explained
before. My problem is that it goes into the PrivateFunction, loops through
that a bunch of times, then does the macro. Then it repeats. My only problem
is it just keeps bringing up the same data from Access because it's not
actually looping through the array when I have it say next ldatarow. Does
anyone know how to fix this?

 

[Guest]

This would cause an error for me (xl2003 and earlier)

Private Function GetCurrentData() 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

GetCurrentData = alTemp

Erase alTemp
End Function


You can only assign an array to a variant.

I would change it to

Private Function GetCurrentData() As Variant

and in RUN

Dim alData() As Long

would be
Dim alData() as Variant

or in xl97 and earlier
Dim alData as Variant

Since you indicate these are all miraculously working, hard to say.


This seems ill advised:
For lDataRow = 0 To UBound(GetCurrentData, 1) - 1
alData = GetCurrentData

Why not call it once unless the values in the Worksheets("mwksResults"),
column A are changing as you process.

alData = GetCurrentData
For lDataRow = 0 To UBound(alData, 1) - 1

 

[Guest]

Thanks Tom, once again for your help.

This would cause an error for me (xl2003 and earlier)

Private Function GetCurrentData() 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

GetCurrentData = alTemp

Erase alTemp
End Function


You can only assign an array to a variant.

I would change it to

Private Function GetCurrentData() As Variant

and in RUN

Dim alData() As Long

would be
Dim alData() as Variant

or in xl97 and earlier
Dim alData as Variant

Since you indicate these are all miraculously working, hard to say.


This seems ill advised:
For lDataRow = 0 To UBound(GetCurrentData, 1) - 1
alData = GetCurrentData

Why not call it once unless the values in the Worksheets("mwksResults"),
column A are changing as you process.

alData = GetCurrentData
For lDataRow = 0 To UBound(alData, 1) - 1