1

I wanted to automate calculations of the sumproduct of two arrays of variables which could iterate for dozens/hundreds of times, and then copy the results to a column, and then repeat the process, and copy the new results to a column next to the previous one. Upon completion of all the iterations, I wanted to add up all the results copied in the columns by row, and copy the sum of each row to each cell in a designated range one by one.

The code seems to be running without warnings, but the major issue seems to be that after each calculation, the results that are copied to the columns would be overwritten by the following calculated results. Also, the calculations seem to be taking some time to complete although it seems to be simple calculations.

Any help with reviewing and advising the following codes is much apprecited!

Sub Macro1()
'
' Macro1 Macro
' calculate total emissions from transport
'

Dim rowcount As Long


rowcount = Worksheets("Building Components").Range("Transport_Raw_Materials").Rows.Count

rowcount_results_total = Worksheets("Building Components").Range("TD_RawMaterial_Results_Total").Rows.Count
rowcount_results_urban = Worksheets("Building Components").Range("TD_RawMaterial_Results_Urban").Rows.Count
rowcount_results_total_items = Worksheets("Factors_T&D").Range("TD_RawMaterial_Results_Total_Items").Rows.Count
rowcount_results_urban_items = Worksheets("Factors_T&D").Range("TD_RawMaterial_Results_Urban_Items").Rows.Count

Dim i As Integer

For i = 1 To rowcount
  Worksheets("Factors_T&D").Range("Truck_ModeShare_Raw_Material") = Worksheets("Building Components").Range("Transport_Raw_Materials").Cells(i, 2).Value
  Worksheets("Factors_T&D").Range("Rail_ModeShare_Raw_Material") = Worksheets("Building Components").Range("Transport_Raw_Materials").Cells(i, 3).Value
  Worksheets("Factors_T&D").Range("Tanker_ModeShare_Raw_Material") = Worksheets("Building Components").Range("Transport_Raw_Materials").Cells(i, 4).Value
  Worksheets("Factors_T&D").Range("Truck_Dist_Raw_Material") = Worksheets("Building Components").Range("Transport_Raw_Materials").Cells(i, 5).Value
  Worksheets("Factors_T&D").Range("Rail_Dist_Raw_Material") = Worksheets("Building Components").Range("Transport_Raw_Materials").Cells(i, 6).Value
  Worksheets("Factors_T&D").Range("Tanker_Dist_Raw_Material") = Worksheets("Building Components").Range("Transport_Raw_Materials").Cells(i, 7).Value


    ActiveCell.FormulaR1C1 = "=SUMPRODUCT(RC[" & -3 - i & "]:RC[" & -1 - i & "],R38C27:R38C29)"
    Worksheets("Factors_T&D").Calculate
    Range("Ae53").Offset(0, i - 1).Select
    Selection.AutoFill Destination:=Range("Ae53:Ae58").Offset(0, i - 1), Type:=xlFillDefault
    Range("Ae53:Ae58").Offset(0, i - 1).Select
    Range("Ae58").Offset(0, i - 1).Select
    Selection.Copy
    Range("Ae60:Ae70").Offset(0, i - 1).Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=12
    Range("Ae72:Ae79").Offset(0, i - 1).Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-12
    Application.CutCopyMode = False
    Range("Ae52").Offset(0, i - 1).Value = Worksheets("Building Components").Range("List_Raw_Materials").Cells(i).Value

    Range("Ae53").Offset(0, i).Select


Next i

For i = 1 To rowcount_results_total
   Worksheets("Building Components").Range("TD_RawMaterial_Results_Total").Cells(i) = WorksheetFunction.Sum(Worksheets("Factors_T&D").Range(Range("TD_RawMaterial_Results_Total_Items").Cells(i), Range("TD_RawMaterial_Results_Total_Items").Cells(i).End(xlToRight)))
Next i

For i = 1 To rowcount_results_urban
   Worksheets("Building Components").Range("TD_RawMaterial_Results_Urban").Cells(i) = WorksheetFunction.Sum(Worksheets("Factors_T&D").Range(Range("TD_RawMaterial_Results_Urban_Items").Cells(i), Range("TD_RawMaterial_Results_Urban_Items").Cells(i).End(xlToRight)))
Next i

Calculate

End Sub
urdearboy
  • 14,439
  • 5
  • 28
  • 58
Harry
  • 11
  • 1
  • Could you please share some steps you did in order to narrow down the calculations? – Serhii Matrunchyk May 13 '20 at 19:16
  • 1
    Sure, thanks for your attention! – Harry May 13 '20 at 19:17
  • Sure, thanks for your attention! I defined quite a few named ranges first. Then I defined a variable to count the number of rows in a named range (TD_RawMaterial_Results_Total) where I wanted to take the values from the cells in each column of the named range (which is an array) row by row. For each row, the values were assigned to six variables as shown in the first for loop. These six variables are actually two sets of arrays (1*3), and then I wanted to calculate the sumproduct of the two arrays starting from the ActiveCell.FormulaR1C1 code. – Harry May 13 '20 at 19:25
  • I suggest creating some intermediate variables to simplify your code a bit, for example create `Dim bldgCompWS As Worksheet; Set bldgCompWS = Worksheets("Building Components")`. This will tend to shorten your code and make it a bit more readable. Also, you can [avoid using `Select` and `Activate`](https://stackoverflow.com/a/10717999/4717755). – PeterT May 13 '20 at 19:27
  • Each sumproduction calculation would generate an array of results stored in a fix column of data (n*1). Then, I wanted to copy the calculated sumproduct results in each row of the fixed column to a separate row, starting from Cell AE53. Then, I assigned the column name to Cell AE52. Then, I repeat the value assignment from a second row of the named range (TD_RawMaterial_Results_Total) to the same six variables, and wanted to repeat the calculations, and copy the new results to a new column next to the previous one (started AF52 this time), and repeat until the last row of the named range. – Harry May 13 '20 at 19:30
  • My code could run through the loop, but the results generated and copied over are not correct (each column of the copied data are the same, and seem to be overwritten by what I would have with the final iteration of the sumproduct calculation). – Harry May 13 '20 at 19:32
  • Thanks, PeterT. I am a green hand in Excel VBA, but can understand and appreciate your suggestion! – Harry May 13 '20 at 19:37

0 Answers0