Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
Excel SUM Bug?
OK, something is going on here in Excel 2007 vs. 2003. I have a cell with a
formula in it that was coded in vbscript: ActiveCell.FormulaR1C1 = "=(R[0]C[-4]+R[1]C[-4])*R[+1]C[2]" When I run this in Excel 2003 or 2007 it puts the formula in effectively as something like "=(A12+A13)*G13". In Excel 2003 it calculates the value of the cell correctly. When I run this in Excel 2007 it puts a big fat 0 in the cell which I can see is wrong based on the contents of A12, A13 and G13. I need to change the formula to "=SUM((A12+A13)*G13)" for Excel 2007 to work. What's going on here? Thanks! -Richard K |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
Excel SUM Bug?
OK, I partially take back what I just said. When I add in the "SUM" part to
the formula via the vbscript it still doesn't work. I actually went to the cell and now took out the "SUM" part (basically taking it back to the way I had it before for Excel 2003) and it calculated correctly. Is something screwed up between vbscript and Excel when putting in the formula? This is very weird and causing me major headaches. "Richard K" wrote: OK, something is going on here in Excel 2007 vs. 2003. I have a cell with a formula in it that was coded in vbscript: ActiveCell.FormulaR1C1 = "=(R[0]C[-4]+R[1]C[-4])*R[+1]C[2]" When I run this in Excel 2003 or 2007 it puts the formula in effectively as something like "=(A12+A13)*G13". In Excel 2003 it calculates the value of the cell correctly. When I run this in Excel 2007 it puts a big fat 0 in the cell which I can see is wrong based on the contents of A12, A13 and G13. I need to change the formula to "=SUM((A12+A13)*G13)" for Excel 2007 to work. What's going on here? Thanks! -Richard K |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
Excel SUM Bug?
This works just fine for me in both 2003 and 2007. Perhaps you have some
additional code that is causing the problem? HTH, Elkar "Richard K" wrote: OK, I partially take back what I just said. When I add in the "SUM" part to the formula via the vbscript it still doesn't work. I actually went to the cell and now took out the "SUM" part (basically taking it back to the way I had it before for Excel 2003) and it calculated correctly. Is something screwed up between vbscript and Excel when putting in the formula? This is very weird and causing me major headaches. "Richard K" wrote: OK, something is going on here in Excel 2007 vs. 2003. I have a cell with a formula in it that was coded in vbscript: ActiveCell.FormulaR1C1 = "=(R[0]C[-4]+R[1]C[-4])*R[+1]C[2]" When I run this in Excel 2003 or 2007 it puts the formula in effectively as something like "=(A12+A13)*G13". In Excel 2003 it calculates the value of the cell correctly. When I run this in Excel 2007 it puts a big fat 0 in the cell which I can see is wrong based on the contents of A12, A13 and G13. I need to change the formula to "=SUM((A12+A13)*G13)" for Excel 2007 to work. What's going on here? Thanks! -Richard K |
#4
Posted to microsoft.public.excel.setup
|
|||
|
|||
Excel SUM Bug?
That's just it. When I click on the field in Excel I can look at the formula
and all looks great BUT it is not accuretly calculating the total. When I add in the SUM (or take it out when I changed my vbscript) it will re-calculate. This has worked great in Excel 2003. It wasn't until I moved it to Excel 2007 that I started having problems. "Elkar" wrote: This works just fine for me in both 2003 and 2007. Perhaps you have some additional code that is causing the problem? HTH, Elkar "Richard K" wrote: OK, I partially take back what I just said. When I add in the "SUM" part to the formula via the vbscript it still doesn't work. I actually went to the cell and now took out the "SUM" part (basically taking it back to the way I had it before for Excel 2003) and it calculated correctly. Is something screwed up between vbscript and Excel when putting in the formula? This is very weird and causing me major headaches. "Richard K" wrote: OK, something is going on here in Excel 2007 vs. 2003. I have a cell with a formula in it that was coded in vbscript: ActiveCell.FormulaR1C1 = "=(R[0]C[-4]+R[1]C[-4])*R[+1]C[2]" When I run this in Excel 2003 or 2007 it puts the formula in effectively as something like "=(A12+A13)*G13". In Excel 2003 it calculates the value of the cell correctly. When I run this in Excel 2007 it puts a big fat 0 in the cell which I can see is wrong based on the contents of A12, A13 and G13. I need to change the formula to "=SUM((A12+A13)*G13)" for Excel 2007 to work. What's going on here? Thanks! -Richard K |
#5
Posted to microsoft.public.excel.setup
|
|||
|
|||
Excel SUM Bug?
Hi Richard
It works fine for me in XL2007, provided Active cell is E12 at the point the code is run. Same for XL2003 -- Regards Roger Govier "Richard K" wrote in message ... OK, I partially take back what I just said. When I add in the "SUM" part to the formula via the vbscript it still doesn't work. I actually went to the cell and now took out the "SUM" part (basically taking it back to the way I had it before for Excel 2003) and it calculated correctly. Is something screwed up between vbscript and Excel when putting in the formula? This is very weird and causing me major headaches. "Richard K" wrote: OK, something is going on here in Excel 2007 vs. 2003. I have a cell with a formula in it that was coded in vbscript: ActiveCell.FormulaR1C1 = "=(R[0]C[-4]+R[1]C[-4])*R[+1]C[2]" When I run this in Excel 2003 or 2007 it puts the formula in effectively as something like "=(A12+A13)*G13". In Excel 2003 it calculates the value of the cell correctly. When I run this in Excel 2007 it puts a big fat 0 in the cell which I can see is wrong based on the contents of A12, A13 and G13. I need to change the formula to "=SUM((A12+A13)*G13)" for Excel 2007 to work. What's going on here? Thanks! -Richard K |
#6
Posted to microsoft.public.excel.setup
|
|||
|
|||
Excel SUM Bug?
Here is my complete vbscript code that I am using.
Worksheets("PctCompleteResults").Cells(iCurRow, iCurCol + 4).Select ActiveCell.FormulaR1C1 = "=SUM((R[0]C[-4]+R[1]C[-4])*R[+1]C[2])" or Worksheets("PctCompleteResults").Cells(iCurRow, iCurCol + 4).Select ActiveCell.FormulaR1C1 = "=(R[0]C[-4]+R[1]C[-4])*R[+1]C[2]" Either way the formula is correct when I look at the cell but it still shows 0 in the cell. I have checked the values of the cells referenced in the formula and the values will never add up to 0. If I use the first method then edit the formula in the cell to the 2nd method it calculates and vice versa. As I said this is strange because I've been using this Excel .xls file for 2 years now and this only started when I was opening in Excel 2007. If you want I'll even send anyone the .xls file for them to look at. Thanks for any help on this one. -Richard K "Roger Govier" wrote: Hi Richard It works fine for me in XL2007, provided Active cell is E12 at the point the code is run. Same for XL2003 -- Regards Roger Govier "Richard K" wrote in message ... OK, I partially take back what I just said. When I add in the "SUM" part to the formula via the vbscript it still doesn't work. I actually went to the cell and now took out the "SUM" part (basically taking it back to the way I had it before for Excel 2003) and it calculated correctly. Is something screwed up between vbscript and Excel when putting in the formula? This is very weird and causing me major headaches. "Richard K" wrote: OK, something is going on here in Excel 2007 vs. 2003. I have a cell with a formula in it that was coded in vbscript: ActiveCell.FormulaR1C1 = "=(R[0]C[-4]+R[1]C[-4])*R[+1]C[2]" When I run this in Excel 2003 or 2007 it puts the formula in effectively as something like "=(A12+A13)*G13". In Excel 2003 it calculates the value of the cell correctly. When I run this in Excel 2007 it puts a big fat 0 in the cell which I can see is wrong based on the contents of A12, A13 and G13. I need to change the formula to "=SUM((A12+A13)*G13)" for Excel 2007 to work. What's going on here? Thanks! -Richard K |
#7
Posted to microsoft.public.excel.setup
|
|||
|
|||
Excel SUM Bug?
Hi Richard
You can mail me a copy of the file if you wish. I am off to bed now, but will take a look tomorrow morning. Send to roger at technology4u dot co dot uk Do the obvious with at and dots. -- Regards Roger Govier "Richard K" wrote in message ... Here is my complete vbscript code that I am using. Worksheets("PctCompleteResults").Cells(iCurRow, iCurCol + 4).Select ActiveCell.FormulaR1C1 = "=SUM((R[0]C[-4]+R[1]C[-4])*R[+1]C[2])" or Worksheets("PctCompleteResults").Cells(iCurRow, iCurCol + 4).Select ActiveCell.FormulaR1C1 = "=(R[0]C[-4]+R[1]C[-4])*R[+1]C[2]" Either way the formula is correct when I look at the cell but it still shows 0 in the cell. I have checked the values of the cells referenced in the formula and the values will never add up to 0. If I use the first method then edit the formula in the cell to the 2nd method it calculates and vice versa. As I said this is strange because I've been using this Excel .xls file for 2 years now and this only started when I was opening in Excel 2007. If you want I'll even send anyone the .xls file for them to look at. Thanks for any help on this one. -Richard K "Roger Govier" wrote: Hi Richard It works fine for me in XL2007, provided Active cell is E12 at the point the code is run. Same for XL2003 -- Regards Roger Govier "Richard K" wrote in message ... OK, I partially take back what I just said. When I add in the "SUM" part to the formula via the vbscript it still doesn't work. I actually went to the cell and now took out the "SUM" part (basically taking it back to the way I had it before for Excel 2003) and it calculated correctly. Is something screwed up between vbscript and Excel when putting in the formula? This is very weird and causing me major headaches. "Richard K" wrote: OK, something is going on here in Excel 2007 vs. 2003. I have a cell with a formula in it that was coded in vbscript: ActiveCell.FormulaR1C1 = "=(R[0]C[-4]+R[1]C[-4])*R[+1]C[2]" When I run this in Excel 2003 or 2007 it puts the formula in effectively as something like "=(A12+A13)*G13". In Excel 2003 it calculates the value of the cell correctly. When I run this in Excel 2007 it puts a big fat 0 in the cell which I can see is wrong based on the contents of A12, A13 and G13. I need to change the formula to "=SUM((A12+A13)*G13)" for Excel 2007 to work. What's going on here? Thanks! -Richard K |
#8
Posted to microsoft.public.excel.setup
|
|||
|
|||
Excel SUM Bug?
Is calculation mode set to manual in 2007?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Richard K" wrote in message ... Here is my complete vbscript code that I am using. Worksheets("PctCompleteResults").Cells(iCurRow, iCurCol + 4).Select ActiveCell.FormulaR1C1 = "=SUM((R[0]C[-4]+R[1]C[-4])*R[+1]C[2])" or Worksheets("PctCompleteResults").Cells(iCurRow, iCurCol + 4).Select ActiveCell.FormulaR1C1 = "=(R[0]C[-4]+R[1]C[-4])*R[+1]C[2]" Either way the formula is correct when I look at the cell but it still shows 0 in the cell. I have checked the values of the cells referenced in the formula and the values will never add up to 0. If I use the first method then edit the formula in the cell to the 2nd method it calculates and vice versa. As I said this is strange because I've been using this Excel .xls file for 2 years now and this only started when I was opening in Excel 2007. If you want I'll even send anyone the .xls file for them to look at. Thanks for any help on this one. -Richard K "Roger Govier" wrote: Hi Richard It works fine for me in XL2007, provided Active cell is E12 at the point the code is run. Same for XL2003 -- Regards Roger Govier "Richard K" wrote in message ... OK, I partially take back what I just said. When I add in the "SUM" part to the formula via the vbscript it still doesn't work. I actually went to the cell and now took out the "SUM" part (basically taking it back to the way I had it before for Excel 2003) and it calculated correctly. Is something screwed up between vbscript and Excel when putting in the formula? This is very weird and causing me major headaches. "Richard K" wrote: OK, something is going on here in Excel 2007 vs. 2003. I have a cell with a formula in it that was coded in vbscript: ActiveCell.FormulaR1C1 = "=(R[0]C[-4]+R[1]C[-4])*R[+1]C[2]" When I run this in Excel 2003 or 2007 it puts the formula in effectively as something like "=(A12+A13)*G13". In Excel 2003 it calculates the value of the cell correctly. When I run this in Excel 2007 it puts a big fat 0 in the cell which I can see is wrong based on the contents of A12, A13 and G13. I need to change the formula to "=SUM((A12+A13)*G13)" for Excel 2007 to work. What's going on here? Thanks! -Richard K |
#9
Posted to microsoft.public.excel.setup
|
|||
|
|||
Excel SUM Bug?
Richard
Using an xls in 2007 is not a good plan, have you tried this same thing in 2007 using an xlsx?? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk blog (non tech): www.nickhodge.co.uk/blog "Richard K" wrote in message ... Here is my complete vbscript code that I am using. Worksheets("PctCompleteResults").Cells(iCurRow, iCurCol + 4).Select ActiveCell.FormulaR1C1 = "=SUM((R[0]C[-4]+R[1]C[-4])*R[+1]C[2])" or Worksheets("PctCompleteResults").Cells(iCurRow, iCurCol + 4).Select ActiveCell.FormulaR1C1 = "=(R[0]C[-4]+R[1]C[-4])*R[+1]C[2]" Either way the formula is correct when I look at the cell but it still shows 0 in the cell. I have checked the values of the cells referenced in the formula and the values will never add up to 0. If I use the first method then edit the formula in the cell to the 2nd method it calculates and vice versa. As I said this is strange because I've been using this Excel .xls file for 2 years now and this only started when I was opening in Excel 2007. If you want I'll even send anyone the .xls file for them to look at. Thanks for any help on this one. -Richard K "Roger Govier" wrote: Hi Richard It works fine for me in XL2007, provided Active cell is E12 at the point the code is run. Same for XL2003 -- Regards Roger Govier "Richard K" wrote in message ... OK, I partially take back what I just said. When I add in the "SUM" part to the formula via the vbscript it still doesn't work. I actually went to the cell and now took out the "SUM" part (basically taking it back to the way I had it before for Excel 2003) and it calculated correctly. Is something screwed up between vbscript and Excel when putting in the formula? This is very weird and causing me major headaches. "Richard K" wrote: OK, something is going on here in Excel 2007 vs. 2003. I have a cell with a formula in it that was coded in vbscript: ActiveCell.FormulaR1C1 = "=(R[0]C[-4]+R[1]C[-4])*R[+1]C[2]" When I run this in Excel 2003 or 2007 it puts the formula in effectively as something like "=(A12+A13)*G13". In Excel 2003 it calculates the value of the cell correctly. When I run this in Excel 2007 it puts a big fat 0 in the cell which I can see is wrong based on the contents of A12, A13 and G13. I need to change the formula to "=SUM((A12+A13)*G13)" for Excel 2007 to work. What's going on here? Thanks! -Richard K |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|