calcuate formula by loop with vba

[nidhipatel]

my userform contain 3 textbox
TextBox1 value = 1
TextBox2 value = 5
TextBox3 value = 10000
sheet1 range("C2") = 120000
and range C3 formula is = C2*(1+6/100)
now
formula start from range C3
my problem is i need formula calucate till TextBox2 value ie. 5 menas formula calucate 5 cells C3:C7 if i enter value in TextBox 2 = 10 than
formual calcuate "C3:C12"

my result is shown as bleow if i enter Textbox2 Value is 5

<tbody>
[TD="class: xl63, align: right"]120000[/TD]

[TD="class: xl63, align: right"]127200[/TD]

[TD="class: xl63, align: right"]134832[/TD]

[TD="class: xl63, align: right"]142922[/TD]

[TD="class: xl63, align: right"]151497

if i enter TextBox2 value Is 10 than restul shown as

​

[/TD]

</tbody>

<tbody>
[TD="class: xl65, align: right"]120000[/TD]

[TD="class: xl65, align: right"]127200[/TD]

[TD="class: xl65, align: right"]134832[/TD]

[TD="class: xl65, align: right"]142922[/TD]

[TD="class: xl65, align: right"]151497[/TD]

[TD="class: xl65, align: right"]160587[/TD]

[TD="class: xl65, align: right"]170222[/TD]

[TD="class: xl65, align: right"]180436[/TD]

[TD="class: xl65, align: right"]191262[/TD]

[TD="class: xl65, align: right"]202737[/TD]

</tbody>

it is not done by manually it is done by vba macro only

 

[nemmi69]

can I just check what the next formula would be to see if i have it right.

 

[Fluff]

How about

Private Sub CommandButton1_Click()
   Range("C3").Resize(Me.TextBox2.Value).Formula = "=c2*(1+6/100)"
End Sub

 

[Fluff]

Or if you don't want the formula

Private Sub CommandButton1_Click()
   With Range("C3").Resize(Me.TextBox1.Value)
      .Value = Evaluate(Replace("if({1},@*(1+6/100))", "@", .Offset(-1).Address))
   End With
End Sub

 

[nemmi69]

Dim TBox1 As Single
Dim TBox2 As Single
Dim TBox3 As Single
Dim FLoop As Integer




Sub RepeatFormula()
TBox1 = ActiveSheet.TextBox1.Value
TBox2 = ActiveSheet.TextBox2.Value
TBox3 = ActiveSheet.TextBox3.Value


ActiveSheet.Range("C2").Value = 120000
For FLoop = 3 To 3 + TBox2
    ActiveSheet.Range("C" & FLoop).Value = ActiveSheet.Range("C" & FLoop - 1).Value * (1 + 6 / 100)
Next FLoop
End Sub

 

[nidhipatel]

Great
perfect answer as i need