Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for code for insertion of dynamic excel formulas
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for code for insertion of dynamic excel formulas
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. Robert wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insertion of formulas in foreign language in english version of ex | Excel Discussion (Misc queries) | |||
How do I do dynamic searching in Excel? | Excel Worksheet Functions | |||
Automatic Row insertion code | Excel Programming | |||
VBA Code- Row Insertion | Excel Programming | |||
dynamic value insertion - possible with menu?? | Excel Programming |