Converting number stored as text VBA problem

[Nuz]

I upgraded from Excel 2002 to 2010 and the VBA code I use cannot anymore convert the numbers stored as text to numbers. I use the technique by multiplying the text values with 1, like below:

Range("IV1").NumberFormat = "General"
Range("IV1").Value = "1"
Range("IV1").Copy
Range(Cells(2, 24), Cells(10000, 26)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, SkipBlanks:=True, Transpose:=False
The original data comes from a database and therefore the numbers seems to be stored as text. The problem is only with the numbers that have a comma (,), like 14,5. With integers there is no such problem.

Is there some problem with the above VBA code since if I make the same operation manually (copy the 1 and pastespecial as value with multiply operation) it works perfectly.

[p45cal]

Try this (untested):sub blah
with Range(Cells(2, 24), Cells(10000, 26))
.value=.value
end with
end sub
otherwise attach a small sample file.

[Nuz]

I have tried that one as well but it doesn't work. (That one worked on Excel 2002 as well but not on Excel 2010).

I'm not sure if that is an Excel version issue. The problem may arise since the database report I use here may have changed is data format slightly since the original version. But anyway the problem is with values containing commas.

[mikerickson]

Try this

Sub Macro1()
Dim oneColumn As Range
For Each oneColumn In Selection.Columns
With oneColumn
.TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
End With
Next oneColumn
End Sub