Performance Question - Access to Excel

[JeffK627]

I'm building an application that stores data in an Access database and displays it in an Excel spreadsheet. The data in the database is stored in the following columns: Product, BusinessUnit, ProductCode, State, Quarter, TypeAUnits, TypeBUnits.

The user wants the data broken out by Quarter, i.e., TypeAUnits and TypeBUnits for Quarter 1 of 2008, then Quarter 2 of 2008, etc. up to the current Quarter. There are over 300,000 total rows in the table, and about 26,000 rows per Quarter.

Where I run into a problem is that there aren't necessarily unit numbers for every Product Code in every Quarter, so I have to match the TypeAUnits and TypeBUnits up with the right ProductCode and State, row-by-row. The Product, BusinessUnit, ProductCode and State columns on the Excel sheet are already populated. This is the code I'm currently using, where rst1 is the recordset obtained by selecting all fields from the table where Quarter = the desired quarter:

For i = 11 To lastRow
  If rst1!ProductCode = Sheet1.Cells(i,3) AND rst1!State = Sheet1.Cells(i,4) Then
    Sheet1.Cells(i,5) = rst1!TypeAUnits
    Sheet1.Cells(i,6) = rst1!TypeBUnits
  End If
Next

This is taking forever to run. I have Screen Updating set to False and Calculation set to XLManual, can anyone think of another way to speed this up?

 

[JeffK627]

*bump*