CODEWITHSUNDEEP
vbaexcel
SK7324
SK7324

Reputation: 21

Change data type of a column from Text to Number

I need to update the data type of a column from Text to Number so that I can use it to perform further calculations. The below code is not working for me. Could you please suggest a workaround to this problem?

Sub ConvertTextToNumber()

    Dim rRange As Range
    Dim rCell As Range
    Dim Rowcount As Long


    Worksheets("Input").Select
    Rowcount = Application.CountA(Range("C:C"))
    Set rRange = ActiveSheet.Range("AQ2:AQ" & Rowcount)

    For Each rCell In rRange.Cells

        rCell.Value = rCell.Value * 1

    Next rCell

End Sub

Upvotes: 0

Views: 1272

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

Try setting the NumberFormat to a numeric format before running that loop.

Set rRange = ActiveSheet.Range("AQ2:AQ" & Rowcount)
rRange.NumberFormat = "#,##0" ' anything but "@" / text

For Each rCell In rRange.Cells
    Debug.Assert IsNumeric(rCell.Value) 'will break if not true
    rCell.Value = rCell.Value * 1
Next rCell

I added that Assert call to break execution if an error is ever encountered; if rRange contains any cell errors, rCell.Value * 1 will raise a type mismatch run-time error.

Alternatively you could just skip error cells, if error cells are supposed to be expected:

For Each rCell In rRange.Cells
    If Not IsError(rCell.Value) Then
        rCell.Value = rCell.Value * 1
    End If
Next rCell

Upvotes: 1

Related Questions