TextToNumbers

[Spoulis19]

could somebody please explain me why the
below code of macro converts
from text to numbers the 24, but not the 24,5

Sub textonumbers()
Dim tempR As Range
'find a blank cell
Set tempR = Cells(ActiveSheet.UsedRange.Count +
1)
'format the selection as a number
Selection.NumberFormat = "0"
'copy tempR, which is a blank cell
tempR.Copy
'add tempR's value to the cells in the selection
Selection.PasteSpecial Paste:=xlValues,
operation:=xlAdd
'clear the clipboard
Application.CutCopyMode = False


End Sub​

 

[RSXchin]

because 24,5 ISN'T a number. unless... is that a foreign usage of a decimal point?

 

[Spoulis19]

because 24,5 ISN'T a number. unless... is that a foreign usage of a decimal point?

Yes that is the decimal point of Greece. The 24,5 is number.

 

[Spoulis19]

Yes that is the decimal point of Greece. The 24,5 is number.

 

[RSXchin]

aHA. You need to go into the options menu, international (or format, I don't know because I have 2003) and change it from "." to ","

 

[Marcelo Branco]

Hi,

try this

Sub test()
    Dim aCell As Range
    
    For Each aCell In Selection
        aCell = aCell + 0
    Next aCell
     
End Sub

Here also , (comma) as decimal point

M.

 

[RSXchin]

I don't think he has excel configured to know "," AS a decimal point. It may still think it is "."

 

[Marcelo Branco]

I don't think he has excel configured to know "," AS a decimal point. It may still think it is "."

My Excel 2010 has , (comma) as decimal point, but Excel behaves strangely.

If you turn-on the macro recorder and do mannualy copy and paste special add it works perfect. But if you run the macro that was recorded it doesnt work...

So i tried the work-around i posted above and it worked perfectly.

M.

 

[Spoulis19]

Dear Marcelo you are absolutely right, your macro works!

If you suggest any books except from mrexcel, j-walk and allbright that I have already read them, please let me know.

Kinds Regards

 

[Marcelo Branco]

Dear Marcelo you are absolutely right, your macro works!

If you suggest any books except from mrexcel, j-walk and allbright that I have already read them, please let me know.

Kinds Regards

Spoulis,

Bill Jelen books are also great, but apart reading books follow the threads in this site and you'll learn a lot!

All the best

M.