I have a spreadsheet that is output from an Access form.

H7 (General format) holds =SUM(H2:H5). This is right-justified.
Autocalculation is on
H2 to H5 (General format) hold what appears to be numbers and is
left-justified.
H7=0
If I reformat H2 to H5 as number with 2 decimals, the appearance doesn't
change and H7 remains at zero.
If I double click on eg H3, then click elsewhere, H7=H3.

So it appears that the numbers in H2 to H5 are actually text.

This all used to work, but some changes have been made to the database and
now we have this problem. If I can't find the root cause (ie why Access it
outputting in this way), how can I programmatically change the values in
column H into numbers?

Ian

Hi Ian,

You can programmatically mimic the manual solution:

Format an empty cell as number (just to be sure)
EditCopy
Select your "numbers"
EditPaste special, check Add

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Ian" wrote in message ...
|I have a spreadsheet that is output from an Access form.
|
| H7 (General format) holds =SUM(H2:H5). This is right-justified.
| Autocalculation is on
| H2 to H5 (General format) hold what appears to be numbers and is
| left-justified.
| H7=0
| If I reformat H2 to H5 as number with 2 decimals, the appearance doesn't
| change and H7 remains at zero.
| If I double click on eg H3, then click elsewhere, H7=H3.
|
| So it appears that the numbers in H2 to H5 are actually text.
|
| This all used to work, but some changes have been made to the database and
| now we have this problem. If I can't find the root cause (ie why Access it
| outputting in this way), how can I programmatically change the values in
| column H into numbers?
|
| Ian
|
|

Thanks, Nick. I hadn't realised that this could be done. I had assumed that,
as the values appear to be numbers, then changing the format to number
should be sufficient. It obviously wasn't and your solution has worked (at
least manually).

I'm still hoping I can get the correct output from Access in the first
place, but otherwise implementation of this method in code will be an easy
fix. In fact, I might implement it anyway for ALL the numeric values in the
sheet, as it isn't going to cause any problems with "working" values anyway.

Ian

"Niek Otten" wrote in message
...
Hi Ian,

You can programmatically mimic the manual solution:

Format an empty cell as number (just to be sure)
EditCopy
Select your "numbers"
EditPaste special, check Add

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Ian" wrote in message
...
|I have a spreadsheet that is output from an Access form.
|
| H7 (General format) holds =SUM(H2:H5). This is right-justified.
| Autocalculation is on
| H2 to H5 (General format) hold what appears to be numbers and is
| left-justified.
| H7=0
| If I reformat H2 to H5 as number with 2 decimals, the appearance doesn't
| change and H7 remains at zero.
| If I double click on eg H3, then click elsewhere, H7=H3.
|
| So it appears that the numbers in H2 to H5 are actually text.
|
| This all used to work, but some changes have been made to the database
and
| now we have this problem. If I can't find the root cause (ie why Access
it
| outputting in this way), how can I programmatically change the values in
| column H into numbers?
|
| Ian
|
|