T
Thomasw
From our ERP-system I can export information to clip board, which can be
pasted to Excel. Unfortunately, the numbers has thousand separation (129
123.00)so Excel think it is text. I can replace the sign for that, Chr(160)
with a blank, and that work good. The format of the cells is numbers and I
can calculate with it in Excel. But when I tries to record this keystroke and
then replay it, it doesn't work. Excel deletes the thousand separator but
the numbers has leading blanks and the cell format is general. The macro
works good in Excel 2000 but not in 2002 and 2003. In 2002 (I think) there
was an enhancement in excel, that display a little green mark in the upper
left corner of the cell if Excel think there is an error. These cells is
marked with this, and if I place the cursor in one of these cells, this
message appears: "The number in this cell is formatted as text or has a
leading apostrophe". When I select "Convert to number", the result is
correct. But how to achieve that with hundreds of cells? I can write a macro
that does the formatting cell by cell, but it take to long time to fix all
cells. The macro line "Cells.Replace What:=Chr(160), Replacement:="",
LookAt:=xlPart" do it correct for all cells rapidly, but not when I create an
icon in the menu bar, that run the macro.
pasted to Excel. Unfortunately, the numbers has thousand separation (129
123.00)so Excel think it is text. I can replace the sign for that, Chr(160)
with a blank, and that work good. The format of the cells is numbers and I
can calculate with it in Excel. But when I tries to record this keystroke and
then replay it, it doesn't work. Excel deletes the thousand separator but
the numbers has leading blanks and the cell format is general. The macro
works good in Excel 2000 but not in 2002 and 2003. In 2002 (I think) there
was an enhancement in excel, that display a little green mark in the upper
left corner of the cell if Excel think there is an error. These cells is
marked with this, and if I place the cursor in one of these cells, this
message appears: "The number in this cell is formatted as text or has a
leading apostrophe". When I select "Convert to number", the result is
correct. But how to achieve that with hundreds of cells? I can write a macro
that does the formatting cell by cell, but it take to long time to fix all
cells. The macro line "Cells.Replace What:=Chr(160), Replacement:="",
LookAt:=xlPart" do it correct for all cells rapidly, but not when I create an
icon in the menu bar, that run the macro.