Applying formula to a range taking a long time.

[Herakles]

Hi

I'm applying a formula to 10000 cell range.

Application.Calculation = xlCalculationManual
strFormula = "=COUNTIFS(Target!$A:$A,CalcTranches!$A2,Target!$C:$C,CalcTranches!$B2,Target!$D:$D,CalcTranches!$C2,Target!$E:$E,CalcTranches!$D2)"
rng.formula = strFormula
Application.Calculation = xlCalculationAutomatic

Despite automatic calculation being off it still takes a long time.

The actual calculation is carried out very quickly.

If I use a simplier formula as below for comparison purposes only it takes very little time.

Application.Calculation = xlCalculationManual
strFormula = "=1"
rng.formula = strFormula
Application.Calculation = xlCalculationAutomatic

Is it the adjusting of the row references that is taking the time.

Is here a way around this problem?

Thanks

 

[jasonb75]

Is here a way around this problem?

Cutting down the size of the ranges in the formula is one way, unless you have 1 million rows of data in the target sheet using full columns is a major cause of inefficiency.

Using 10k rows in the formula ranges reduces the time taken to about 1 second, 100k rows takes about 5 seconds.

 

[Herakles]

Cutting down the size of the ranges in the formula is one way, unless you have 1 million rows of data in the target sheet using full columns is a major cause of inefficiency.

Using 10k rows in the formula ranges reduces the time taken to about 1 second, 100k rows takes about 5 seconds.

Thanks for responding jasonb75.

Do you mean cutting Target!$A:$A down to Target!$A2:A5000, for example, in the formula will reduce the amount of time taken?

It makes sense that it will.

I'll give it a go.

 

[jasonb75]

Do you mean cutting Target!$A:$A down to Target!$A2:A5000, for example, in the formula will reduce the amount of time taken?

That is correct, remember that you will need to do all of the Target ranges in the formula so that they are of equal size.
Setting screen updating to false may also help, but I don't see it being a noticeable difference here.

 

[Herakles]

That is correct, remember that you will need to do all of the Target ranges in the formula so that they are of equal size.
Setting screen updating to false may also help, but I don't see it being a noticeable difference here.

Hi Jason

That is a lot better. Thanks.

I just did one range and then offset it to the right when building up the formula.

I've just got one challenge left but I'll put that into another post as I should.

I'm organising shifts for volunteers at COVID-19 Vax Clinics and learning a lot about Excel in the process.

Thanks again.