Assign value depending on position in formula
 

I use the following code to change my formula from:
=SUM(OFFSET(C50,-6,78,-12,1))/SUM(OFFSET(C50,-6,-1,-12,1))
to
=SUM(OFFSET(C50,-7,80,-12,1))/SUM(OFFSET(C50,-7,-3,-12,1))
Sub AdjustNew12Formulas()
U = 6
R = 78
L = 1
For x = 1 To 28
ActiveCell.FormulaR1C1 = _
"=SUM(OFFSET(RC,-" & U & "," & R & ",-12,1))/SUM(OFFSET(RC,-" & U &
",-" & L & ",-12,1))"
ActiveCell.Offset(0, 2).Range("A1").Select

U = U + 1
R = R - 1
L = L + 2
Next x

What I need at this point is a way to determine what my variables are to
begin with. In other words, if the first part of the formula is
SUM(OFFSET(C50,-6,78,-12,1)) I want it to set the starting point for U at 6,
starting point for R as 78, etc...

Assign value depending on position in formula
 

Assuming that the formula is already in the activecell...
U = mid(activecell.formula,instr(1,activecell.Formula, ",-")+2,1)

That should get you going. Let me know if you need exact code.

HTH

Die_Another_Day
StephanieH wrote:
I use the following code to change my formula from:
=SUM(OFFSET(C50,-6,78,-12,1))/SUM(OFFSET(C50,-6,-1,-12,1))
to
=SUM(OFFSET(C50,-7,80,-12,1))/SUM(OFFSET(C50,-7,-3,-12,1))
Sub AdjustNew12Formulas()
U = 6
R = 78
L = 1
For x = 1 To 28
ActiveCell.FormulaR1C1 = _
"=SUM(OFFSET(RC,-" & U & "," & R & ",-12,1))/SUM(OFFSET(RC,-" & U &
",-" & L & ",-12,1))"
ActiveCell.Offset(0, 2).Range("A1").Select

U = U + 1
R = R - 1
L = L + 2
Next x

What I need at this point is a way to determine what my variables are to
begin with. In other words, if the first part of the formula is
SUM(OFFSET(C50,-6,78,-12,1)) I want it to set the starting point for U at 6,
starting point for R as 78, etc...

Assign value depending on position in formula
 

Yay. That worked.

Thanks for your help!

Here's the finished product for anyone interested:

Sub AdjustNew12Formulas()
U = Mid(ActiveCell.Formula, InStr(1, ActiveCell.Formula, ",-") + 2, 1)
R = Mid(ActiveCell.Formula, InStr(1, ActiveCell.Formula, ",-") + 4, 2)
L = Mid(ActiveCell.Formula, InStr(1, ActiveCell.Formula, ",-") + 34, 1)
For x = 1 To 28
ActiveCell.FormulaR1C1 = _
"=SUM(OFFSET(RC,-" & U & "," & R & ",-12,1))/SUM(OFFSET(RC,-" & U &
",-" & L & ",-12,1))"
ActiveCell.Offset(0, 2).Range("A1").Select

U = U + 1
R = R - 1
L = L + 2
Next x

End Sub


"Die_Another_Day" wrote:

Assuming that the formula is already in the activecell...
U = mid(activecell.formula,instr(1,activecell.Formula, ",-")+2,1)

That should get you going. Let me know if you need exact code.

HTH

Die_Another_Day
StephanieH wrote:
I use the following code to change my formula from:
=SUM(OFFSET(C50,-6,78,-12,1))/SUM(OFFSET(C50,-6,-1,-12,1))
to
=SUM(OFFSET(C50,-7,80,-12,1))/SUM(OFFSET(C50,-7,-3,-12,1))
Sub AdjustNew12Formulas()
U = 6
R = 78
L = 1
For x = 1 To 28
ActiveCell.FormulaR1C1 = _
"=SUM(OFFSET(RC,-" & U & "," & R & ",-12,1))/SUM(OFFSET(RC,-" & U &
",-" & L & ",-12,1))"
ActiveCell.Offset(0, 2).Range("A1").Select

U = U + 1
R = R - 1
L = L + 2
Next x

What I need at this point is a way to determine what my variables are to
begin with. In other words, if the first part of the formula is
SUM(OFFSET(C50,-6,78,-12,1)) I want it to set the starting point for U at 6,
starting point for R as 78, etc...