| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with a formula if yes calculate and if no 0 | Excel Discussion (Misc queries) | |||
| Formula to calculate 401(k) | Excel Worksheet Functions | |||
| Formula does not calculate - Have tried F9 | Excel Worksheet Functions | |||
| formula does not calculate | Excel Discussion (Misc queries) | |||
| Calculate formula only once. | Excel Discussion (Misc queries) | |||
Linear Mode
