I create 35 sets of the following formulas (also see the union part below):

Range("E7").Formula = "=COUNTIFS(Last100!$C:$C,E$2,Last100!$M:$M,'Data'!E$3,Last100!$A:$A,'Data'!$D7)"

Range("F7").Formula = "=COUNTIFS(Last100!$C:$C,E$2,Last100!$M:$M,'Data'!E$3,Last100!$H:$H,$D7)*-1"

Range("G7").Formula = "=COUNTIFS(Last100!$A:$A,""<=""&'Data'!$D7,Last100!$H:$H,"">""&'Data'!$D7,Last100!$C:$C,'Data'!E$2,Last100!$M:$M,'Data'!E$3)+COUNTIFS(Last100!$A:$A,""<=""&'Data'!$D7,Last100!$H:$H,""=""&""31/12/2000"",Last100!$C:$C,'Data'!E$2,Last100!$M:$M,'Data'!E$3)"

Range("H7").Formula = "=SUMIFS(Sierra_Alpha!$E:$E,Sierra_Alpha!$A:$A,'Data'!$D7,Sierra_Alpha!$B:$B,'Data'!E$2,Sierra_Alpha!$C:$C,'Data'!E$3)" 'ok

Range("I7").Formula = "=SUMIFS(Sierra_Alpha!$D:$D,Sierra_Alpha!$A:$A,'Data'!$D7,Sierra_Alpha!$B:$B,'Data'!E$2,Sierra_Alpha!$C:$C,'Data'!E$3)" 'ok

Range("J7").Formula = "=COUNTIFS(Last100!$C:$C,E$2,Last100!$M:$M,'Data'!J$3,Last100!$A:$A,'Data'!$D7)" 'ok

Range("K7").Formula = "=COUNTIFS(Last100!$C:$C,E$2,Last100!$M:$M,'Data'!J$3,Last100!$H:$H,$D7)*-1"     'ok

Range("L7").Formula = "=COUNTIFS(Last100!$A:$A,""<=""&'Data'!$D7,Last100!$H:$H,"">""&'Data'!$D7,Last100!$C:$C,'Data'!E$2,Last100!$M:$M,'Data'!J$3)+COUNTIFS(Last100!$A:$A,""<=""&'Data'!$D7,Last100!$H:$H,""=""&""31/12/2000"",Last100!$C:$C,'Data'!E$2,Last100!$M:$M,'Data'!J$3)"

Range("M7").Formula = "=SUMIFS(Sierra_Alpha!$E:$E,Sierra_Alpha!$A:$A,'Data'!$D7,Sierra_Alpha!$B:$B,'Data'!E$2,Sierra_Alpha!$C:$C,'Data'!J$3)"

Range("N7").Formula = "=SUMIFS(Sierra_Alpha!$D:$D,Sierra_Alpha!$A:$A,'Data'!$D7,Sierra_Alpha!$B:$B,'Data'!E$2,Sierra_Alpha!$C:$C,'Data'!J$3)"

Imagine that formulas (as you can see) are aligned in a row Range=E7:N7 Now, this range is copied into another 35 rXa's, thus e.g. formulas from r1a=E7:N7, are copied into r2a which covers another 10 columns and so on until r35a. Then I use autofill to fill in 100 rows. Then Excel calculates (and this bit takes approx 15 mins), after that I replace formulas with values. Thus I end up with a large table at the end. Thus, I need an advice on how to make it more efficient please.

Union part:

Set raUnion = Union(r1a, r2a, r3a, r4a, r5a, r6a, r7a, r8a, r9a, r10a, r11a, r12a, r13a, r14a, r15a, r16a, r17a)

Set raUnion2 = Union(r18a, r19a, r20a, r21a, r22a, r23a, r24a, r25a, r26a, r27a, r28a, r29a, r30a, r31a, r32a, r33a, r34a, r35a)

Set raUnionOFUnions = Union(raUnion, raUnion2)

Set UnionaR = Range("E7:MZ7")

It takes approximately 15 minutes to run it. Can someone advise on a more efficient alternative please?

1 Answers

0
West Ray On Best Solutions

Once I have restarted my computer it takes less than 3 mins lol. Sorry

Panic is over