Excel SUM Bug?

[Richard K]

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

 

[Richard K]

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.

 

[Elkar]

This works just fine for me in both 2003 and 2007. Perhaps you have some
additional code that is causing the problem?

HTH,
Elkar

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.

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

 

[Richard K]

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.

This works just fine for me in both 2003 and 2007. Perhaps you have some
additional code that is causing the problem?

HTH,
Elkar

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.

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

 

[Roger Govier]

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

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.

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

 

[Richard K]

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

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

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.

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

 

[Roger Govier]

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

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

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

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.

:

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

 

[Bob Phillips]

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)

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

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

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.

:

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

 

[Nick Hodge]

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
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog

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

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

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.

:

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