Searching for code for insertion of dynamic excel formulas

[Robert]

Hi,

I am trying to insert 1997 excel formulas by vba code.

I came this far..
__________________________________
Private Sub InsertFormulas()

Dim y As Integer

yy = "If(xx="";0;xx+$E$2)"

For y = 3 To 2000

Range("I" & y).Formula = yy

xx = Range("I" & CStr(y - 1))

Next y

End Sub
__________________________________

The only problem is xx in the formula, how can I program this that in
stead of XX the CELLNUMBERS (I3, I4, I5 etc) substitutes in the
formula.

I dont know how to fix this because the Excelformula is inserted as
Text.

Robert

 

[Dave Peterson]

Are you trying for a formula like this in I2: =IF(I2="",0,I2+$E$2)

If yes...

You can actually do this in one fell swoop:

ActiveSheet.Range("I3:I2000").Formula = "=if(I2="""",0,i2+$e$2)"

You write the formula for the first cell in the range and excel will adjust it
for you.

But you could do this (xl2k or higher, since Replace was added in xl2k):

Option Explicit
Private Sub InsertFormulas()
Dim y As Long
Dim yy As String
Dim zz As String

yy = "=If(xx="""",0,xx+$E$2)"

For y = 3 To 2000
zz = Replace(yy, "xx", Range("I" & y - 1).Address(0, 0))
Range("I" & y).Formula = zz
Next y

End Sub

Inside your formula:
yy = "=If(xx="""",0,xx+$E$2)"
You'll want to start with an equal sign.
You'll want to double up those double quotes.
And since VBA is USA centric, you'll want to use commas, not semicolons as the
list separator.