|
Why does the Formula in VBA not calculate
I have some code that plugs in a formula. The formula is not calculating
and instead is showing up as the code itself. This is the formula: ActiveCell.FormulaR1C1 = _ "=IF(OR(RC[-2]=Cond1,RC[-2]=Cond2,RC[-2]=Cond3,RC[-2]=Cond4,RC[-2]=Cond5,RC[-2]=Cond6,RC[-2]=Cond7,RC[-2]=Cond8,RC[-2]=Cond9,RC[-2]=Cond10,RC[-2]=Cond11,RC[-2]=Cond12,RC[-2]=Cond13),""C&I"",RC[1])" Once the code runs, the cells show this: =IF(OR(RC[-2]=Cond1,RC[-2]=Cond2,RC[-2]=Cond3,RC[-2]=Cond4,RC[-2]=Cond5,RC[-2]=Cond6,RC[-2]=Cond7,RC[-2]=Cond8,RC[-2]=Cond9,RC[-2]=Cond10,RC[-2]=Cond11,RC[-2]=Cond12,RC[-2]=Cond13),"C&I",RC[1]) instead of the results. Note: COND1, cond2, cond3, cond4, etc. are a named range in another sheet. Can anyone please tell me why the code is not calculating? As a side note....the cells are formatted as text after code run....and general before the code runs. |
Why does the Formula in VBA not calculate
I'd make sure that the activecell is formatted as General before you plop in the
formula. with activecell .numberformat = "General" .formular1c1 = "=...." end with petedacook wrote: I have some code that plugs in a formula. The formula is not calculating and instead is showing up as the code itself. This is the formula: ActiveCell.FormulaR1C1 = _ "=IF(OR(RC[-2]=Cond1,RC[-2]=Cond2,RC[-2]=Cond3,RC[-2]=Cond4,RC[-2]=Cond5,RC[-2]=Cond6,RC[-2]=Cond7,RC[-2]=Cond8,RC[-2]=Cond9,RC[-2]=Cond10,RC[-2]=Cond11,RC[-2]=Cond12,RC[-2]=Cond13),""C&I"",RC[1])" Once the code runs, the cells show this: =IF(OR(RC[-2]=Cond1,RC[-2]=Cond2,RC[-2]=Cond3,RC[-2]=Cond4,RC[-2]=Cond5,RC[-2]=Cond6,RC[-2]=Cond7,RC[-2]=Cond8,RC[-2]=Cond9,RC[-2]=Cond10,RC[-2]=Cond11,RC[-2]=Cond12,RC[-2]=Cond13),"C&I",RC[1]) instead of the results. Note: COND1, cond2, cond3, cond4, etc. are a named range in another sheet. Can anyone please tell me why the code is not calculating? As a side note....the cells are formatted as text after code run....and general before the code runs. -- Dave Peterson |
Why does the Formula in VBA not calculate
are you looking for: Activecell.FormulaR1C1="=IF(OR(RC[-2]=Cond1,RC[-2]=Cond2,RC[-2]=Cond3,RC[-2]=Cond4,RC[-2]=Cond5,RC[-2]=Cond6,RC[-2]=Cond7,RC[-2]=Cond8,RC[-2]=Cond9,RC[-2]=Cond10,RC[-2]=Cond11,RC[-2]=Cond12,RC[-2]=Cond13),"& """C&I""" & ",RC[1])" ? petedacook;435310 Wrote: I have some code that plugs in a formula. The formula is not calculating and instead is showing up as the code itself. This is the formula: ActiveCell.FormulaR1C1 = _ "=IF(OR(RC[-2]=Cond1,RC[-2]=Cond2,RC[-2]=Cond3,RC[-2]=Cond4,RC[-2]=Cond5,RC[-2]=Cond6,RC[-2]=Cond7,RC[-2]=Cond8,RC[-2]=Cond9,RC[-2]=Cond10,RC[-2]=Cond11,RC[-2]=Cond12,RC[-2]=Cond13),""C&I"",RC[1])" Once the code runs, the cells show this: =IF(OR(RC[-2]=Cond1,RC[-2]=Cond2,RC[-2]=Cond3,RC[-2]=Cond4,RC[-2]=Cond5,RC[-2]=Cond6,RC[-2]=Cond7,RC[-2]=Cond8,RC[-2]=Cond9,RC[-2]=Cond10,RC[-2]=Cond11,RC[-2]=Cond12,RC[-2]=Cond13),"C&I",RC[1]) instead of the results. Note: COND1, cond2, cond3, cond4, etc. are a named range in another sheet. Can anyone please tell me why the code is not calculating? As a side note....the cells are formatted as text after code run....and general before the code runs. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120844 |
Why does the Formula in VBA not calculate
yes...that is the formula I have. It is seperated by _
which is a line break. "p45cal" wrote: are you looking for: Activecell.FormulaR1C1="=IF(OR(RC[-2]=Cond1,RC[-2]=Cond2,RC[-2]=Cond3,RC[-2]=Cond4,RC[-2]=Cond5,RC[-2]=Cond6,RC[-2]=Cond7,RC[-2]=Cond8,RC[-2]=Cond9,RC[-2]=Cond10,RC[-2]=Cond11,RC[-2]=Cond12,RC[-2]=Cond13),"& """C&I""" & ",RC[1])" ? petedacook;435310 Wrote: I have some code that plugs in a formula. The formula is not calculating and instead is showing up as the code itself. This is the formula: ActiveCell.FormulaR1C1 = _ "=IF(OR(RC[-2]=Cond1,RC[-2]=Cond2,RC[-2]=Cond3,RC[-2]=Cond4,RC[-2]=Cond5,RC[-2]=Cond6,RC[-2]=Cond7,RC[-2]=Cond8,RC[-2]=Cond9,RC[-2]=Cond10,RC[-2]=Cond11,RC[-2]=Cond12,RC[-2]=Cond13),""C&I"",RC[1])" Once the code runs, the cells show this: =IF(OR(RC[-2]=Cond1,RC[-2]=Cond2,RC[-2]=Cond3,RC[-2]=Cond4,RC[-2]=Cond5,RC[-2]=Cond6,RC[-2]=Cond7,RC[-2]=Cond8,RC[-2]=Cond9,RC[-2]=Cond10,RC[-2]=Cond11,RC[-2]=Cond12,RC[-2]=Cond13),"C&I",RC[1]) instead of the results. Note: COND1, cond2, cond3, cond4, etc. are a named range in another sheet. Can anyone please tell me why the code is not calculating? As a side note....the cells are formatted as text after code run....and general before the code runs. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120844 |
Why does the Formula in VBA not calculate
Look _very__carefully, it is not the same. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120844 |
Why does the Formula in VBA not calculate
I think Dave hit the nail on the head (or is it the head on the nail). Make
sure the cells are NOT formatted as Text. If the formatting is Text, Excel will not perform any calculations for you. Format as Dave suggested, or right-click the cell and choose your formatting preference (but not Text). One quick way to tell if the cell is formatted as Text is to look at it and if the characters are left justified, the cell is formatted as Text. If the characters are right justified, the cell is not formatted as Text. HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "p45cal" wrote: Look _very__carefully, it is not the same. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120844 |
Why does the Formula in VBA not calculate
p45cal;438764 Wrote: Look _very_ carefully, it is *not *the same. I suggest copying and pasting - it worked here. Yes, my apologies, although the formulae are different, they produce the same result, so please ignore my posts on this one! -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120844 |
| All times are GMT +1. The time now is 09:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com