Help with VBA needed

[elmacay]

Hi guys,

I need to calculate a rather intricate index formula in 43,000 cells. This our servers can't handle, it takes up too much power. So I would like to loop the following steps in a macro:

1. Copy the formula in cells A1-A10 (10 at a time works well)
2. Paste the formulae in A11-A20
3. Copy A11-A20
4. Paste as value in A11-A20
-------
Loop 2
5. Copy formula A1-A10
6. Paste in A21-A30
7. Copy A21-A30
8. Paste as value in A21-A30
-------
Loop 3
Etc.

Any help is greatly appreciated!

Cheers, Elmacay

 

[ChrisBM]

'Counter for the loops
For i = 1 to 4300
         'Copy A 1 to A 10
         Range("A1:A10").Copy
         'Paste Formulas in next block
         Range("A" & (((i-1)*10)+11)).PasteSpecial Paste:=xlPasteFormulas
         'Copy Next block
         Range("A" & (((i-1)*10)+11) & ":A" & (((i-1)*10)+20)).Copy
         'Paste as static
         Range("A" & (((i-1)*10)+11)).PasteSpecial Paste:=xlPasteValues
Next

Hope this helps,

Kind Regards,
Chris

 

[elmacay]

It does work, thanks a lot!