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