Duplicating Formulas

[Guest]

My problem is when i enter a number into a cell i want a formula to be
inserted underneath this cell. The formula will entered that number of
times. My formula is
2*3.14*(A1+B1)/2
This formula is entered in the first cell
The next cell will be
2*3.14*(a1+b1+b1)/2
B1 will be added twice and then in the next cell B1 will be added three
times and so on until the number in that was entered is reached.
Anybody got any ideas?

 

[Guest]

I would add an auxilary column to the spreadsheet that has the numbers 1,
2,3,...

Row Column A Column B Column C
1 =2*3.14*(A1+(C1 * B1))/2 1
2 =2*3.14*(A2+(C2 * B2))/2 2
3 =2*3.14*(A3+(C3 * B3))/2 3
4 =2*3.14*(A4+(C4 * B4))/2 4
5 =2*3.14*(A5+(C5 * B5))/2 5

You can also use the row function instead of adding the column
=2*3.14*(A1+(row(A1) * B1))/2

 

[Mike Fogleman]

Here is an example using C1 as the number to duplicate the formula:

Sub DupFormula()
Dim i As Long
Dim rng As Range

i = Range("C1").Value 'change to your cell

For i = 1 To i
Set rng = Cells(i + 1, 3) 'reference to your cell
rng.Formula = "=2*3.14*(A1+(B1*" & i & "))/2"
Next
End Sub

Mike F