Why does the Formula in VBA not calculate

[petedacook]

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]

I'd make sure that the activecell is formatted as General before you plop in the
formula.

with activecell
.numberformat = "General"
.formular1c1 = "=...."
end with

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.

 

[petedacook]

yes...that is the formula I have. It is seperated by _
which is a line break.

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])"

?

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.

 

[ryguy7272]

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---