Macro, Activate.Paste vs. Ctrl-v.

**Jiser001** · 11-13-2009, 04:48 PM

This problem is somewhat related to my pervious post on this forum; With an europeean version of Excel, I have the following two numbers stored in my clipboard;

21,6
19,60454

When I use the Ctrl-v the numbers paste nicely into my worksheet, but when I run the following simple macro;

Sub Makro1()
'
ActiveSheet.Paste
'
End Sub

The numbers comes out as;

21,6
1 960 454

There is an information tag on cell 1, saying “this number is formatted as a text….”.

Does anyone recognize this “problem” and knows how to fix it..?

Thanks.

**Simon Lloyd** · 11-14-2009, 07:23 AM

Originally Posted by Jiser001

......
The numbers comes out as;

21,6
1 960 454

There is an information tag on cell 1, saying “this number is formatted as a text….”.

Does anyone recognize this “problem” and knows how to fix it..?

Thanks.

Format the cells as number, type in your 2 numbers, copy to clipboard, future pastes should be fine however , your number format 19,60454 isn't valid, it should be 1,960,454 that is probably why excel thinks its text rather than a number!

**teylyn** · 11-14-2009, 07:31 AM

@simon

your number format 19,60454 isn't valid, it should be 1,960,454 that is probably why excel thinks its text rather than a number!

I disagree. The OP mentions European Excel, where the decimal point is actually a decimal comma, so

19,60454 in European is 19.60454 in US/UK settings, which is a perfectly valid number format.

Jiser, check your Windows regional settings, and also your Excel settings for delimiters!

cheers

**Jiser001** · 11-14-2009, 09:08 AM

Thanks to both of you.

Unfortunately I was not precise when describing the environment I am working in. But Teylyn is right, the two numbers, - which are part of a huge series of numbers copied from a third part application, are valid numbers in my regional version of Excel. So the expected values are numbers; 21.6 and 19.60454 respectively. In my europeean Excel version denoted with a “,” as decimal separator instead of the “.”.

When I paste these two numbers into my worksheet using “Ctrl-v”, it works perfect and my VB code can work further with them as numbers. However, if I use the VB code “ActiveSheet.Paste” to paste the content into my worksheet, it does not perform an instruction identical to “Ctrl-v”, and Excel treat the first number as text and multiply the last number with 10,000. Altering the regional settings in the controlpanel or altering the delimiters in Excel only alter the appearance of the outcome. Still the format is wrong. Any idea..?

**dominicb** · 11-14-2009, 10:13 AM

Good afternoon Jiser001

I can't be sure how it works for numbers, and I'm trying to reach down in the dark recesses of the old grey matter here, but ...

I seem to remember having a similar problem with dates and after much messing around came to the conclusion that while your worksheets are happy to work in the locally accepted format, VBA likes to work in the US format. So where us Europeans like to see dd/mm, the US format is mm/dd. This could be the situation with your numbers, ie UK / US format is 1,234.56 and the European format is 1.234,56.

This doesn't really help you as I can't provide a sure workaround - rather than using paste, could you not read the value of the cells and copy them elsewhere, or copy using Format rather than paste. This is all just theoretical as I don't have access the European Excel ...

HTH

DominicB

**Jiser001** · 11-14-2009, 11:12 AM

Dear DominicB

Yes I think you are on to something.

It seems to me as when the VB(with English text menus) has control and running the “ActiveSheet.Paste”, the Excel environment are already altered, and the content in clipboard is interpreted different than in the Excel environment when I past with “Ctrl-v”. Does this make sense..?

As for you pervious “problem”, I think that Teylyn remedy may work, - changing the regional setting in the control panel to US. But to my problem, that had no effect as the first number is regarded as text using the “ActiveSheet.Paste”, and to the second number change of regional settings only add thousand separator to the last number, but still interpret it wrong…