I am trying to clean up some old slow code which is causing a bottleneck but I can't seem to figure this part out. The existing code applies a SUMIF formula to data on a tab called 'Lists', with criteria columns in columns A and B of the 'Lists' tab. The sum column is variable, with the column number of the sum range in the 'Lists' tab shown by the calculated value in column I of my current worksheet. The existing approach uses INDIRECT to specify the sum range in each formula, however this (along with the following steps of copy/paste/autofill/calculate) is slowing the code down a lot:
I think I can do this all in one step using FormulaR1C1 but I can't work out how to reference the value in column I to specify the column number needed for the sum range - Any idea hows to obtain the value XXXXXX in the below?
VBA Code:
Range("K5").Formula = "=SUMIFS(INDIRECT(""Lists!C""&$I5,0),Lists!$A:$A,$G5,Lists!$B:$B,K$1)"
Range("K5").Copy
Range("L5:R5").PasteSpecial xlPasteAll
Range("G5:R5").AutoFill Range("G5:R2000")
Cells.Calculate
I think I can do this all in one step using FormulaR1C1 but I can't work out how to reference the value in column I to specify the column number needed for the sum range - Any idea hows to obtain the value XXXXXX in the below?
VBA Code:
Range("K5:R2000").FormulaR1C1 = "=SUMIFS(Lists!C" & XXXXXX & ":C" & XXXXXX & ", Lists!C1:C1,RC7,Lists!C2:C2,R1C)"