Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to add depending upon value entered | Excel Worksheet Functions | |||
Same formula, different results depending on machine? | Excel Worksheet Functions | |||
Formula Depending On Formatting | Excel Discussion (Misc queries) | |||
Formula to look for value in a row depending on unknown value? | Excel Worksheet Functions | |||
Value or formula in cell depending on input... | Excel Programming |