Hi,

I am struggling with a problem with Excel 2000 although I suspect it
may be the same for other versions.

I have a spreadsheet which has a column which has numbers in its
cells.

When I try loading this using TransferSpreadsheet a number of the cells
fail to load because of "Type conversion errors"

Looking at the problem fields they all appear to be numbers with
nothing to indicate that they are anything other than numbers.

When I run this macro in Excel
(http://j-walk.com/ss/excel/tips/tip28.htm) it displays the data type
that the SpecialCells function thinks are in problem cells.
The macro reports that the SpecialCells says they are text values.

If I manually clear the contents and retype the values then they are
numbers as they should be.

A *nasty* side effect of manually changing these is that if I then save
the file Excel complains that it is broken and needs to repair itself.
It tries to reinstall the VBA6.msi file but that fails with an install
errors 2709 & 1603.

My IT helpdesk is looking at it but I suspect this will be beyond
them.

Does anyone know:
a) What has caused this problem in the first place
b) If there is an easy way of solving this without having to write a
macro that loops around all the cells and checks what they and changes
them as necessary
c) How I can stop this in the future when the users enter the data...

Cheers,

Mark

see if this helps

Sub FixRangeValues()
For Each c In Selection
c.Value = Format(c, "00")
Next
End Sub
"robbinma" wrote in message
...
Hi,

I am struggling with a problem with Excel 2000 although I suspect it
may be the same for other versions.

I have a spreadsheet which has a column which has numbers in its
cells.

When I try loading this using TransferSpreadsheet a number of the cells
fail to load because of "Type conversion errors"

Looking at the problem fields they all appear to be numbers with
nothing to indicate that they are anything other than numbers.

When I run this macro in Excel
(http://j-walk.com/ss/excel/tips/tip28.htm) it displays the data type
that the SpecialCells function thinks are in problem cells.
The macro reports that the SpecialCells says they are text values.

If I manually clear the contents and retype the values then they are
numbers as they should be.

A *nasty* side effect of manually changing these is that if I then save
the file Excel complains that it is broken and needs to repair itself.
It tries to reinstall the VBA6.msi file but that fails with an install
errors 2709 & 1603.

My IT helpdesk is looking at it but I suspect this will be beyond
them.

Does anyone know:
a) What has caused this problem in the first place
b) If there is an easy way of solving this without having to write a
macro that loops around all the cells and checks what they and changes
them as necessary
c) How I can stop this in the future when the users enter the data...

Cheers,

Mark

Make sure the cells are not formatted as Text (format=Cells=Number
Format).

If the user is entering them by hand, then formatting the cells as Text
would be the only thing I can think of that would cause them to be stored as
text when they are numeric in nature. If the user put a single quote in
front of the number, this would cause that effect as well - perhaps the
users are old Lotus 1-2-3 users and are putting in the singlequote as a
formatting character.

--
Regards,
Tom Ogilvy


robbinma wrote in message
...
Hi,

I am struggling with a problem with Excel 2000 although I suspect it
may be the same for other versions.

I have a spreadsheet which has a column which has numbers in its
cells.

When I try loading this using TransferSpreadsheet a number of the cells
fail to load because of "Type conversion errors"

Looking at the problem fields they all appear to be numbers with
nothing to indicate that they are anything other than numbers.

When I run this macro in Excel
(http://j-walk.com/ss/excel/tips/tip28.htm) it displays the data type
that the SpecialCells function thinks are in problem cells.
The macro reports that the SpecialCells says they are text values.

If I manually clear the contents and retype the values then they are
numbers as they should be.

A *nasty* side effect of manually changing these is that if I then save
the file Excel complains that it is broken and needs to repair itself.
It tries to reinstall the VBA6.msi file but that fails with an install
errors 2709 & 1603.

My IT helpdesk is looking at it but I suspect this will be beyond
them.

Does anyone know:
a) What has caused this problem in the first place
b) If there is an easy way of solving this without having to write a
macro that loops around all the cells and checks what they and changes
them as necessary
c) How I can stop this in the future when the users enter the data...

Cheers,

Mark

Changing the cell format does not change the cell value (in particular,
if value was text before, it will still be text after). Changing the
cell format does prepare the cell to receive a number however, so you
could re-enter the data, and it would then be interpreted as a number.
Alternately, as your helpdesk knew, math operators will coerce text into
numbers; thus
=5+"3"
will return 8.

As to why the numbers were text to begin with, there are many
possibilities. The cells may have been formatted as text before the
"numbers" were entered. The "numbers" may have been entered with a
leading single quote. The "numbers" may have been copied from another
workbook, where they were text. The numbers may have been exported from
a database using Crystal Report or similar tools. ...

Jerry

robbinma wrote:

Thanks.

I tried doing the reformatting but it still had the problem.
The solution (from my local helpdesk) was to enter a 1 in a seperate
cell and then do paste special-Multiply to reset the values.

Still no idea about what caused the problem in the first place.

Regards,

Mark