Thanks Tom, once again for your help.
"Tom Ogilvy" wrote:
> 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
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Need Help Fast!" wrote:
>
> > 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?
|
Similar Threads
