Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=SUBSTITUTE(C4,"~?#","") will this work to remove multiple string | Excel Worksheet Functions | |||
How do I "insert copied cells"? used to be "alt i e" still work | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
=IF(A1=MAX($A$1:$A$3),A1,"") Adapt to use multiple "if" formula's | Excel Discussion (Misc queries) | |||
pictures to work with "data" "sort" option | Excel Discussion (Misc queries) |