conversion to formula of string contaning IF clause

[JeanBQ]

I have been using a large macro in Excel 2002 for some years now. One
step in the macro consists of creatng a formula.

An expression for a formula, using labels, is constructed as a string.
This is then converted into a formula by,

effectively, the following lines

formule = "=" & Selection.Value
Selection.FormulaR1C1 = formule

, where formule is a variable declared as String.

This method has always worked fine. However, recently I started using
formulas containing the IF( A, B, C) expression.

The macro stiil runs without errors messages. However, for the new type
of formulas the value is not emmediately calulated: the #NAME?
expression is shown instead. The formula is only evaluated after
selecting the cell, putting the cursor behind the formula and pressing
Enter.

It appears that the cell is not aware that the contents is a formula
and needs to be activated or awakened.

I have found several topics in this group relating to this issue, but
my problem seem to go beyond a basic string to

formula conversion.

What is happening, and what should be done to correct this.

[K Dales]

The sheet is not recalculating (I think perhaps it does not recognize that
the cell now has precedents - other cells whose values the results depend on)

The solution is to put a Worksheet("SheetName").Calculate right after you
reset the cell formula.
--
- K Dales


"JeanBQ" wrote:

> I have been using a large macro in Excel 2002 for some years now. One
> step in the macro consists of creatng a formula.
>
> An expression for a formula, using labels, is constructed as a string.
> This is then converted into a formula by,
>
> effectively, the following lines
>
> formule = "=" & Selection.Value
> Selection.FormulaR1C1 = formule
>
> , where formule is a variable declared as String.
>
> This method has always worked fine. However, recently I started using
> formulas containing the IF( A, B, C) expression.
>
> The macro stiil runs without errors messages. However, for the new type
> of formulas the value is not emmediately calulated: the #NAME?
> expression is shown instead. The formula is only evaluated after
> selecting the cell, putting the cursor behind the formula and pressing
> Enter.
>
> It appears that the cell is not aware that the contents is a formula
> and needs to be activated or awakened.
>
> I have found several topics in this group relating to this issue, but
> my problem seem to go beyond a basic string to
>
> formula conversion.
>
> What is happening, and what should be done to correct this.
>
>