How do I output a formula into a cell and then autofill using vb?
 

Hi!
I need to output a formula into a cell and then autofill. My problem is that
I don't know how far down this will go because it depends on a parameter that
the user has chosen. I will also need to sum this column. i.e.
I would normally write a loop:
For i=1 to n
Cells(i,1)=(1+(Cells(i,2))^2/Cells(1,3)
Next i ..etc
But I need the actual formula in the cell not just the value because I will
then be using Solver so the cells need to be connected to each other. Is this
possible?

How do I output a formula into a cell and then autofill using vb?
 

Try this:


Sub AddFormula()

Dim n As Integer, i As Integer

Const Formula As String = "=1+BX^2/C1"

For i = 1 To n
Cells(i, 1) = Replace(Formula, "X", i)
Next i

End Sub

HTH



"Cardiff Maths Student" wrote:

Hi!
I need to output a formula into a cell and then autofill. My problem is that
I don't know how far down this will go because it depends on a parameter that
the user has chosen. I will also need to sum this column. i.e.
I would normally write a loop:
For i=1 to n
Cells(i,1)=(1+(Cells(i,2))^2/Cells(1,3)
Next i ..etc
But I need the actual formula in the cell not just the value because I will
then be using Solver so the cells need to be connected to each other. Is this
possible?

How do I output a formula into a cell and then autofill using vb?
 

Another:

Sub AddFormula()
Dim n As Integer, i As Long
n = Inputbox("enter row of last cell for formula")
if n = 0 then exit sub
Const Form As String = "=1+B1^2/$C$1"
Cells(1, 1).Resize(n,1) = Form
cells(n+1,1).FormulaR1C1 = "=Sum(R1C:R[-1]C)"
End Sub

or

Sub AddFormula()
Dim n As Integer, i As Long
n = Cells(Rows.Count, 2).End(xlUp).Row
If n = 0 Then Exit Sub
Const Form As String = "=1+B1^2/$C$1"
Cells(1, 1).Resize(n, 1) = Form
Cells(n + 1, 1).FormulaR1C1 = "=Sum(R1C:R[-1]C)"
End Sub


But the formula (Const Form) may need to be adjusted since the original
formula posted had unbalanced parentheses.

--
Regards,
Tom Ogilvy


"Toppers" wrote in message
...
Try this:


Sub AddFormula()

Dim n As Integer, i As Integer

Const Formula As String = "=1+BX^2/C1"

For i = 1 To n
Cells(i, 1) = Replace(Formula, "X", i)
Next i

End Sub

HTH



"Cardiff Maths Student" wrote:

Hi!
I need to output a formula into a cell and then autofill. My problem is
that
I don't know how far down this will go because it depends on a parameter
that
the user has chosen. I will also need to sum this column. i.e.
I would normally write a loop:
For i=1 to n
Cells(i,1)=(1+(Cells(i,2))^2/Cells(1,3)
Next i ..etc
But I need the actual formula in the cell not just the value because I
will
then be using Solver so the cells need to be connected to each other. Is
this
possible?