Formula's don't work until I "touch" them?
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 |
Formula's don't work until I "touch" them?
"Maury Markowitz" wrote in message ... 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 |
Formula's don't work until I "touch" them?
"Fredrik Wahlgren" wrote:
There's an easier way. Do a Find&Replace and replace all equal signs with equal signs. That worked. Ok, why? Maury |
Formula's don't work until I "touch" them?
"Maury Markowitz" wrote in message ... "Fredrik Wahlgren" wrote: There's an easier way. Do a Find&Replace and replace all equal signs with equal signs. 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 |
All times are GMT +1. The time now is 06:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com