VBA converting text to numbers

Resolved · High Priority · Version 2007

Tue 24 Aug 2010, 15:04 Delegate Jack said...

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

Tue 31 Aug 2010, 08:51 Trainer Stephen said...

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

Tue 31 Aug 2010, 15:37 Delegate Jack said...

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