Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Recorded replacement doesn't work
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
recorded macros | Excel Worksheet Functions | |||
Recorded macro | Excel Discussion (Misc queries) | |||
I recorded a macro "PrintSetUp" in excel but does not work | Excel Worksheet Functions | |||
How to FIX Recorded Macro | Excel Discussion (Misc queries) | |||
Recorded Macro | New Users to Excel |