Excel - Formula: Missing value on Formula Evaluation on currency cell

Scripting Forums AppleScript | Mac OS X
,
#1

Hey all,

I get a “missing value” value when I try to get the value from a cell that is formatted with currency. With no a “general” format I get the value of 15

ex.
cells a1 to a5 are
1
2
3
4
5

cell A6 is =sum(a1:a5)


tell application "Microsoft Excel"
	set sourceValue to value of cell "A6"
end tell

Anyone know how to get the value of a field with a number format?

thanks

#2

thanks but that turns an integer to a string which makes it kinda difficult to use it in another calculation

#3

Thanks Jacques. However that works but I would have to do that to numerous cells which can be very difficult when I have over a few hundred cells with similar cells. Is there way to maybe do a work around that has an applescript that can change the currency indicator before you obtain the cell value and then change it back?

thanks for all the help as always.

#4

Only problem is that the evaluation works for A2 but A6 has the formula which doesn’t seem to be working.

with error:

Applescript Error
“=SUM(A1:A5)” into type real

thanks

#5

As always Jacques thanks alot! :slight_smile:

Too bad the applescript excel reference manual is so encryptic

#6

I think this will work better. What it does is it takes the value and makes it into a general number then you can do what you want with it then set the format back to the original. $15.00


tell application "Microsoft Excel"
	set number format of range "A6" to "General"
	set sourceValue to value of cell "A6"
	set printValue to sourceValue
	set number format of range "A6" to "$#,##0.00_);($#,##0.00)"
end tell