Inputting multiple formula without selecting cells

Question

Hi,

The following is just a small extract from a large number of linked subroutines and it is beginning to slow down and crash for some reason.

For starters, I'm aiming to remove all select statement starting here:

How could I re-write this to avoid using the select statements

Range("C1").Select

    ActiveCell.FormulaR1C1 = "=COUNT(R[2]C:R[19999]C)/14"

    Range("D1").Select

    ActiveCell.FormulaR1C1 = "=COUNT(R[2]C:R[19999]C)/14"

    Range("E1").Select

    ActiveCell.FormulaR1C1 = "=COUNT(R[2]C:R[19999]C)/14"

    Range("F1").Select   ........................etc etc

Many thanks

Answer 1

To avoid Selecting each cell, you could write the formulas directly:

    Range("C1").FormulaR1C1 = "=COUNT(R[2]C:R[19999]C)/14"

    Range("D1").FormulaR1C1 = "=COUNT(R[2]C:R[19999]C)/14"

    Range("E1").FormulaR1C1 = "=COUNT(R[2]C:R[19999]C)/14"

    Range("F1").FormulaR1C1 = ........................etc etc   

But I suspect it's the numerous calculations that are slowing you down - since calculating the results of all of those Count formulas will eventually eat up a lot of CPU.  Try turning off Calculation while you put the formulas in place, then turn it on again to do a single re-calc after you're done:

    Application.Calculation = xlCalculationManual

    Range("C1").FormulaR1C1 = "=COUNT(R[2]C:R[19999]C)/14"

    Range("D1").FormulaR1C1 = "=COUNT(R[2]C:R[19999]C)/14"

    Range("E1").FormulaR1C1 = "=COUNT(R[2]C:R[19999]C)/14"

    Range("F1").FormulaR1C1 = ........................etc etc

   Application.Calculation = xlNormal

Also, you might consider turning off Screen Updates, which also slow things down considerably.  Try this:

Application.ScreenUpdating = False

(all your code here)

Application.ScreenUpdating = True

Answer 2

The first step would be

Range("C1").FormulaR1C1 = "=COUNT(R[2]C:R[19999]C)/14"

Range("D1").FormulaR1C1 = "=COUNT(R[2]C:R[19999]C)/14"

Range("E1").FormulaR1C1 = "=COUNT(R[2]C:R[19999]C)/14"

etc.

But since your formulas are all the same, you can simplify this to

Range("C1:Z1").FormulaR1C1 = "=COUNT(R[2]C:R[19999]C)/14"

where C1:Z1 is the range you want to add the formula to.

By the way, this looks like a question about Excel, not Word, and about the full version, not the Starter Edition (there's no VBA in the Starter Edition)

Answer 3

You can edit the first post in this thread and change Word to Excel, and Office 2010 Starter to Office 2010.

Answer 4

Hi,

Many thanks for your help.

I had already turned off screen updating but had not done the same for calculations (I had thought calculation had to be left on if formula were involved) but I see what you mean about the points at which it is turned on and off - very helpful advice, I will now certainly do that!

Answer 5

Hi,

Many thanks for the advice - really helpful, especially about grouping sets of identical formula.

Much appreciated.

Sorry, I thought I'd posted to Excel, not Word.  It is Excel 2010 and it is the full version (of course) - selected "starter" because didn't see full version listed in drop down box.