Anyone know of a faster calculation for the CPU???

[mdavidge]

Hello,

I have a macro that pastes formulas. The macro described below takes 1 hour and 20 minutes to run. I am wondering if anyone knows of a better way to perform these calculations in Excel. The calculation appears to tax the CPU and the this is the time killer.

The spead sheet is rather large with roughly 65,000 rows and 170 columns. I have 140 columns with the SUMPRODUCT formula listed below in the second row. My macro selects an adjasent area with 920 rows and pastes 140 colums with SUMPRODUCT (formulas like below) down with the SUMPRODUCT formulas. The lookup data is what makes up the 65,000 rows. This calculation of 920 rows X 140 colums takes the CPU 1 hour and 20 minutes to complete.

=SUMPRODUCT(--($A$4:$A$65000=$Y4),--($L$4:$L$65000="SEALS"),($M$4:$M$65000))

I have build the calculation in Access only to find that Excel cannot transfer more than 50 Fields from an Access Query back to Excel in an automated way. I have built both ADO code and tried Import External Data. Both will return the info if pulled from a Access Table, but not from a Query.

Any help is appreciated.

Mike

 

[Norie]

Mike

Can we see the code?

 

[mdavidge]

Please forgive the Northwind references as I lifted the code from MS Help site. The ADO code is just below. Trafers tables and queries with less than 50 fields without flaw. Cannot deal with more than 50 from a query, and neither can Import External Data. I thinks it may be an inherant limitation with transfering Access Query.

Sub AGetFinalReport()

'Only returns 50 fields from Access Query ZFINAL.  Will return 50 and less Fields wo issues from Access Query.
'Will return all (160 Fields?) from Table with same info.
   'Create a Recordset from all the records in the ZFINAL Query.
   Dim sNWind As String
   Dim conn As New ADODB.Connection
   Dim rs As ADODB.Recordset
   sNWind = _
      "C:\SALES REPORT CRUNCHER.mdb"
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      sNWind & ";"
   conn.CursorLocation = adUseClient
   Set rs = conn.Execute("ZFINAL", , adCmdTable)

   'Transfer the data to Excel
   ActiveSheet.Range("A2").CopyFromRecordset rs

   'Close the connection
   rs.Close
   conn.Close


End Sub

 

[Norie]

Mike

If you already have the calculations in Access, why do you need to import the data into Excel?

By the way you said your macro pasted formulas. I can't see anywhere in the posted code that pastes a formula.

 

[Aladin Akyurek]

Hello,

I have a macro that pastes formulas. The macro described below takes 1 hour and 20 minutes to run. I am wondering if anyone knows of a better way to perform these calculations in Excel. The calculation appears to tax the CPU and the this is the time killer.

The spead sheet is rather large with roughly 65,000 rows and 170 columns. I have 140 columns with the SUMPRODUCT formula listed below in the second row. My macro selects an adjasent area with 920 rows and pastes 140 colums with SUMPRODUCT (formulas like below) down with the SUMPRODUCT formulas. The lookup data is what makes up the 65,000 rows. This calculation of 920 rows X 140 colums takes the CPU 1 hour and 20 minutes to complete.

=SUMPRODUCT(--($A$4:$A$65000=$Y4),--($L$4:$L$65000="SEALS"),($M$4:$M$65000))

I have build the calculation in Access only to find that Excel cannot transfer more than 50 Fields from an Access Query back to Excel in an automated way. I have built both ADO code and tried Import External Data. Both will return the info if pulled from a Access Table, but not from a Query.

Any help is appreciated.

Mike

Do all of the SumProduct formulas test just the ranges in column A and column L?

 

[mdavidge]

Here is the code that pastes the formulas.

Sub Test()

'  Select row 4 AE:IT where the math is
    Range("AE4:IT4").Select
    Selection.Copy
'  RIGHT COLUMN - Enter column here ("#65536") for your right boundary to be selected.
    Range("AD65536").Select
    Selection.End(xlUp).Select
'  (# of row move down from bottom / - # moves up, # of columns to select right of Range("Column").Select above / -# move left from)
    Selection.Offset(0, 1).Range("A1").Select
'  (TOP ROW, LEFT COLUMN) Cells(#top row, #left column)
    Range(Selection, Cells(5, 254)).Select
'  Pastes formulas only into only non-blank area.
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.Copy
'  Pastes Values only into selected area
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False



End Sub

 

[mdavidge]

Aladin,

Criteria 1 $A is always the same. Criteria 2 and the Sum columns move. Criteria 2 can be columns J, K or L. The sum Criteria varies from M, N or O.

Mike

 

[Aladin Akyurek]

Aladin,

Criteria 1 $A is always the same. Criteria 2 and the Sum columns move. Criteria 2 can be columns J, K or L. The sum Criteria varies from M, N or O.

Mike

If Criteria 2 moves, why do you have ="SEALS" instead of a cell reference?

 

[mdavidge]

Norie,

This is sales information and none of our sales force is familiar with Access. I want to take this 920 row X 160 colum area of information and write code to manipulate and sort it in various ways. The sales force will then get a file with this information and simply make choices from lists and buttons to manipulate this information in various ways.

Mike

 

[Norie]

Mike

I don't know if it helps at all, but you don't need to do all that selecting.

Perhaps something like this?

Dim LastRow As Long
    Application.ScreenUpdating=False
    Range("AE4:IT4").Copy
    
    LastRow = Range("AD65536").End(xlUp).Row
    
    Range("AE4:AE" & LastRow).PasteSpecial Paste:=xlPasteFormulas
    Range("AE4:IT" & LastRow).PasteSpecial Paste:=xlPasteValues
Application.ScreenUpdating=True