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?

 

[Nuz]

No effect with that. Nothing happens on the values when I run the code.

I found that if I replace the periods (.) with slashes (/) with VBA, some of the values take a date form. For example, the text value of 1.9.2011 becomes the date of 9.1.2011. This is obviously a wrong date since the day and month are in wrong order (should be 1st of September) but at least the value is now in a date format.

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

Is it possible that the source report has some workbook-specific regional settings in it? Or can I utilize this somehow to get the correct dates?

 

[RoryA]

Did you try running the Text to columns manually?
What date separators does your machine normally use?

 

[Nuz]

I ran text to colums manually and it works when the separator is defined as ".".

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

The above (recorded) code inserts the day, month and year to three worksheet columns but what I'd like to have is to replace the initial text value in each cell with a date value.

How should I modify the code?

 

[RoryA]

You don't want to define the separator as anything in the string or you will get separate columns.

 

[Andrew Poulsom]

This worked for me (Excel 2003):

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 21/10/2011 by Andrew
'
'
    Columns("A:A").Select
    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
    Columns("A:A").EntireColumn.AutoFit
End Sub

Before and after:

1.9.2011 01/09/2011
31.12.2011 31/12/2011

 

[Nuz]

Thanks for your help.
It works now perfectly.