CRTL+D macro (very large amount of rows needed)

**wlan11g** · 10-10-2009, 10:13 AM

Hello guys,

See the attached excel file,

I've done the first 200 or so rows by hand, (well when I say by hand, I really mean by CTRL+D). Anyways, I need to generate the same recursive forumlas for 230400 rows!!!

- I have formulas in the cell columns A,B,C,D & E (look from row 18 downwards)
- Can ignore last two columns, as I will be importing data from a separate file

so i'll need something like this for the remaining rows:

for cell rows 201 to 230400;
   for cell columns A to E;
      do formula calculation; 
         next column
   next row

n.b. Each column (A to E) has different formulas,

Thanks in advance!

**JBeaucaire** · 10-10-2009, 11:13 AM

This macro will insert your formulas directly into the range of rows you designate with the BR and LR variables at the top of the macro. I set this to 1000 rows to test, but you can change those values to what you want.

Option Explicit

Sub AddFormulas()
Dim BR As Long, LR As Long

BR = 201
LR = 1000

Range("A" & BR, "A" & LR).FormulaR1C1 = "=SUM(1,R[-1]C1)"
Range("B" & BR, "B" & LR).FormulaR1C1 = "=MOD(SUM(R[-11]C2,R[-18]C2),2)"
Range("C" & BR, "C" & LR).FormulaR1C1 = "=MOD(SUM(R[-8]C3,R[-11]C3,R[-13]C3,R[-18]C3),2)"
Range("D" & BR, "D" & LR).FormulaR1C1 = "=MOD(SUM(MOD(RC2,POWER(2,18)-1)+RC3),2)"
Range("E" & BR, "E" & LR).FormulaR1C1 = "=IF(RC4=0,1,-1)"

End Sub

**royUK** · 10-10-2009, 11:13 AM

Versions of Excel less than 2007 do not have that manuy rows

This will fill the remaining rows with the formulas, although I can't imagine why you need to.

Option Explicit

Sub x()
Dim rng As Range

Set rng = Range(Cells(201, 1), Cells(Rows.Count, 5))
rng.Formula = Range(Cells(200, 1), Cells(200, 5)).Formula
End Sub

**D_Rennie** · 10-10-2009, 11:33 AM

Not to take anthink away from the code as its alot faster.

Though instead of doing each cell indivually using ctrl+D you can grap the lower right corrner of the cell and drag it down with the mouse, or copy the cell with the formular and then ctrl+shift+down right click paste special formulars.
just though ild though that up cos it seam that you done 200 rows with ctrl+D

**wlan11g** · 10-10-2009, 05:05 PM

Thanks for all your the help guys.

excel 2003 has a max. of 2^16 (65536) rows, whereas excel 2007 has 1mil +

looks like its time for an upgrade.

**royUK** · 10-11-2009, 04:04 AM

The code that I suggested will work with 2007, however don't get carried away adding formulas to complete colmns, it is bound to slow calculation speeds.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**JBeaucaire** · 10-11-2009, 04:04 AM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)