changing the currenc setting of a cell or range

[Guest]

I have a macro that copies data from other workbooks. The problem is that the
data always displays with the $ sign when it should be the £ sign. The source
data is defined as £. when I go in the format, options and select currency
and £ it will not change the cell.

Most annoying, please help

Chris

 

[Frank Kabel]

Hi
how do you copy your values. Seems like the values are not stored as
numbers anymore

 

[Dave Peterson]

Did you verify that the values with dollar signs are really text?

If yes, you could select the range (a column or all the cells) and do:

Edit|replace
what: $ (dollar sign)
with: (leave blank)
replace all

If it's successful when you do it manually, record a macro and add that to the
bottom of your code.

 

[Guest]

Thanks Dave
I will try this. In the mean time I replaced some code with the following
mybook.Activate
sourceRange.Select
Selection.Copy
basebook.Activate
destrange.Select
Selection.PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False,
Transpose:=False

This works but I always get the Clipboard poping up saying do I want to save
the data to the clipboard Yes/No Cancel. Is there any way to surpress this
message?

Chris

 

[Guest]

Tried the Edit|Replace and it works, but leaves me with the drop down box
with the option convert to number. All cells have the little green flag on
them. I tried to create a macro that would then select the range and do the
convert to number but doesn't work, any ideas

Chris

 

[Dave Peterson]

It sounds like the values are still text. Can you format the cells as General
first, then do the edit|replace?

(test manually as a quick check.)

For what it's worth, xl2002 (USA version), if I edit|replace some characters out
of a cell formatted as text and leave only numeric characters, then the value is
numeric.

Any chance you have a decimal point problem, too (comma vs dot)?

I'd guess no, since that green warning flag already showed up.

 

[Guest]

What I've done is edit the macro to use Edit, Replace and then turn off the
error checking under Tools Options and the green flags disapear, may be a
liitle cheating but it works. I tried formatting the columns first but does
not work. Is it possible to turn of the Clipboard so it does not keep poping
up asking if you wish to keep data for later?

Chris

 

[Dave Peterson]

Application.cutcopymode = false

should help.

Chip Pearson has an addin that can show you what's in the cell (character by
character).
http://www.cpearson.com/excel/CellView.htm

If there's something weird in there, it might help you find it.

What I've done is edit the macro to use Edit, Replace and then turn off the
error checking under Tools Options and the green flags disapear, may be a
liitle cheating but it works. I tried formatting the columns first but does
not work. Is it possible to turn of the Clipboard so it does not keep poping
up asking if you wish to keep data for later?

Chris

 

[Guest]

Thanks this works fine. I'll test my new macro on Excel 97 and see. People
with old unsupported software, what shall we do with them???

Chris