On Sheet1, I have a 5 row x 2 column table of factors, which are 2 multiplicative factors for each of 5 different categories, in cells B1:C5.
Sheet2 has a set of data in Column C grouped by the 5 categories and the past 12 months, so 60 rows total. The same factors are getting used for the corresponding category in each month, i.e. Row 1 of factors in Sheet1 will be applied to Row 1, Row 6, Row 11 etc. of Sheet2.
Calculations are currently set to Manual (there will be a lot of formulas that are getting calculated and I would like to save that until the end of the macro). So my question is, is there a way to have each month's categories reference the proper factors without a loop and without copying and pasting for maximum efficiency? Essentially I need to reference cells B1:C5 of Sheet1 for multiplication in D1:E60 in Sheet2.
Right now I perform this with a loop for the first 5 rows and then copy and paste it down to the 60th row as follows:
For row = 1 to 5
Sheet2.Range("D" & row).Formula = "=$C" & row & "*Sheet1!$B$" & row
Sheet2.Range("E" & row).Formula = "=$D" & row & "*Sheet1!$C$" & row
Next row
Sheet2.Range("D1:E5").Copy
Sheet2.Range("D1:E60").PasteSpecial xlPasteFormulas
This is actually a simplification of my problem and there are actually tens of thousands of cells being calculated in this way with hundreds of categories across several years, which is why I ask about efficiency. Is there a way to do this across the whole range with FormulaR1C1 perhaps? I apologize if things are unclear as English is not my first language.
Aucun commentaire:
Enregistrer un commentaire