Jack has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Excel VBA Advanced course
VBA converting text to numbers
Hi there,
I want to convert a column of numbers stored as text to numbers, i tried using the macro recorder but the coding wasnt picked up.
I tried the following codes but didn't have much luck:
For Each Cell In Selection
Cell.Value = CDec(xCell.Value)
Next Cell
Any help is appreciated!
Thanks
Jack
RE: VBA converting text to numbers
Hi jack
Thanks fopr your question
The following code seems to do the trick
Dim i As Integer
For i = 1 To Range("a1").CurrentRegion.Rows.Count
Cells(i, 5).Value = CDec(Cells(i, 1).Value)
Next i
hope this helps
Regards
Stephen
RE: VBA converting text to numbers
Hi Stephen,
thanks for the email.
I have modified the codes slighty to stop the process at 2nd last row:
Dim i As Integer
Sheets("sheet4").Activate
For i = 1 To Range("A1").End(xlDown).Offset(-1, 0)
Cells(i, 2).Value = CDec(Cells(i, 1).Value)
Next i
This code runs fine and the numbers are copied to column 2 but i get a run time error 13 - type mismatch and the following line is highlighted.
Cells(i, 2).Value = CDec(Cells(i, 1).Value)
everything looks fine and not sure why this is highlighted, the reason i want it to stop at 2nd row from last is because last row is text.
thanks
jack