Formula's don't work until I "touch" them?

[Guest]

I have a script that creates a series of formulas in a particular column in a
sheet. The exact formula needs to change depending on the machine it's run
on: on machines running Bloomberg it has to look like "=BLP|blabla", on
machines running Reuters it's "=BDDE|someotherblabla". I find out which to
use by looping over the AddIns list and looking for the plugins.

The code is pretty simple, I turn off autoupdates, loop over the cells and
put in the formula, then turn on autoupdates again. To put it into the cells
I use something like "theCell.Formula = theFormulaString"

However, this does not work. After turning on updates every cell reports
#N/A. This is easy to fix, all I have to do is click on the cell and then
press the checkmark in the edit bar -- note, I don't have to actually CHANGE
anything. This isn't a "solution" though, because there's 1000 rows in the
sheet.

Anyone know why this happens, and how to fix it?

Maury

 

[Fredrik Wahlgren]

I have a script that creates a series of formulas in a particular column in a
sheet. The exact formula needs to change depending on the machine it's run
on: on machines running Bloomberg it has to look like "=BLP|blabla", on
machines running Reuters it's "=BDDE|someotherblabla". I find out which to
use by looping over the AddIns list and looking for the plugins.

The code is pretty simple, I turn off autoupdates, loop over the cells and
put in the formula, then turn on autoupdates again. To put it into the cells
I use something like "theCell.Formula = theFormulaString"

However, this does not work. After turning on updates every cell reports
#N/A. This is easy to fix, all I have to do is click on the cell and then
press the checkmark in the edit bar -- note, I don't have to actually CHANGE
anything. This isn't a "solution" though, because there's 1000 rows in the
sheet.

Anyone know why this happens, and how to fix it?

Maury

Do the formulas work once you have touched them or do you have to repeat the
process? There's an easier way. Do a Find&Replace and replace all equal
signs with equal signs.


/Fredrik

 

[Guest]

There's an easier way. Do a Find&Replace and replace all equal
signs with equal signs.

That worked. Ok, why?

Maury

 

[Fredrik Wahlgren]

That worked. Ok, why?

Maury

I think you insert the fourmulas as text. It is perfectly possible to do so.
When you replace an equal sign with an equal sign, Excel will understand
that what begins with an equal sign should be treated as a formula.
Essentially the same as when you "touch" them.

/Fredrik