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?

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?

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" <[email protected]> wrote in message
news:[email protected]...
> 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?