PasteSpecial multiply doesn't work with Excel 2010

[Nuz]

In Excel 2002 I used the VBA code that selected a range and multiplied that range by a value of 1.
When changed to Excel 2010, this code does not work anymore.
The code is:

Range("A1").FormulaR1C1 = "1"
Range("A1").Copy
Range(Cells(2, 1), Cells(100, 1)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, SkipBlanks:=True, Transpose:=False

If I do it manually, it works in Excel 2010 but the above VBA code cannot do it.

I also used this:

Selection.Value = Selection.Value

But it doesn't work either.

The worksheet where I try to apply this is a database report where certain numbers are in text format and therefore I need to convert them to numbers before using the in my models.
I also noticed that the probelm occurs when the initial value has either a comma (,) or point (.) in it. For example, a text 21,1 cannot be converted but the text 21 can.
Also dates, like 31.12.2012 have the same problem.

Can this be fixed somehow?

 

[Andrew Poulsom]

Try removing the quotes around "1". They make it a text entry.

 

[Nuz]

I tried that. It didn't work.

When I replace the commas with points through VBA like that:

Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

...the text values instantly become numerical.

However, if I do the same replacing manually, the values become dates.
For example, the initial text value of 14,5 becomes 14.5.2011 if done manually but becomes correctly a decimal value if done through VBA (with the code above).

I really don't understand why this behaves so since I recorded the above macro. So it should do exactly the same thing that doing it manually.

 

[Andrew Poulsom]

No, I mean in this:

Range("A1").FormulaR1C1 = "1"

 

[Nuz]

I tried exactly that but it didn't work.

 

[RoryA]

I really don't understand why this behaves so since I recorded the above macro. So it should do exactly the same thing that doing it manually.

It should but that is not always the case, since VBA assumes US regional settings by default. Since the period is not a date separator in the US, it does not convert to dates when you run the code.

 

[Nuz]

I see.

Within dates, in every cell I now need to manually go to formula bar and press enter. After doing so, the dates are displayed correctly as dates (and aligned to right). I kind of have to re-enter the value to a cell.
I still think this is due to the report that is extracted from the database.

Can I similarly "re-enter" the values vith VBA somehow? I have over 10,000 rows to go through.

 

[RoryA]

You can usually just select all the data, use Data-Text to columns, Delimited and in step 3 specify the date order. That will do all of them at once.

 

[Nuz]

Could you give an example code of that?

 

[RoryA]

For example:

    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True

this assumes DMY date order.