05-01-2008, 08:01 AM
I'm new to VB for excel but here is my question. I want to insert a formula where the row will never change but the column will. The formula looks something like this: ActiveCell.FormulaR1C1 = "=R[-1]C/R[XX])]C" I need the XX to always be row12 but i can't seem to get it. Any help provided would be greatly appreciated.
05-01-2008, 02:41 PM
Build a string with the complete formula, then enter the string into the cell.
for x = 1 to 5
stringA = "=R[-1]C/RC" & x ' build a string with the number "x" at the end
activecell.offset(x -1 ,0).formula = stringA ' enter the string into a cell as a formula
This code will fill the selected cell and the next four below it with these formulas:
Of course, you'll need to adapt the string and variables to build exactly the formulas you want.
Note: your example has a closing ")" but no opening "(".
Incomplete () or  pairs will prevent a formula from working.
You can make a string from as many pieces as you need:
strMyString = "some text" & variableX & "some other text" & variableX & "more text" & variableY etc.
05-03-2008, 02:32 AM
I may have misunderstood the question, but are you just looking for an explanation of how R1C1 formulas work?
R = Current Row
R = Row 12 down from current row
R12 = Row 12
R3C = C$4
RC4 = $D2
R3C3 = $C$3