Excel Function inside FOR loop.

[jalves428]

Please help. Why this little peace of VBA in a EXCEL
project Work Well if inside the PPMT Function ,instead
of i ,we put one numeric integer number, or a cell (with
a integer number), but not with the i, wich is what we
need to actualize each line?


Sub Button2_Click()

Dim i As Integer

For i = 1 To 30
Range("e" & i).Value = "=PPMT((0.08/12), i ,30,10000,1)"
Next i

End sub

 

[dbKemp]

Please help. Why this little peace of VBA in a EXCEL
project Work Well if inside the PPMT Function ,instead
of i ,we put one numeric integer number, or a cell (with
a integer number), but not with the i, wich is what we
need to actualize each line?

Sub Button2_Click()

Dim i As Integer

For i = 1 To 30
Range("e" & i).Value = "=PPMT((0.08/12), i ,30,10000,1)"
Next i

End sub

Try this:
Dim i As Integer
Dim sFormula As String

For i = 1 To 30
sFormula = "=PPMT((0.08/12), " & i & " ,30,10000,1)"
Range("e" & i).Formula = sFormula
Next i

 

[jalves428]

Thank you very very much.The "secret" you write
is perffect. I try assignate directly, without the sFormula variable,
and
also is Working. This is:

   Dim i As Integer
 REM  Dim sFormula As String

 For i = 1 To 30
Range("e" & i).Formula = "=PPMT((0.08/12), " & i & " ,30,10000,1)"
 Next i