VBA help to Convert tex number of diffrent solumn to number

[Mallesh23]

Hi Team, 

I am trying to convert the the number which are in text to number
Numbers in few column(C:C and I:I) getting deleted.

below are two method getting same result, 

plz suggest any best method to tackle this task.

Sub Convert_TextNumber_Number()


Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

ws.Range("A:A,C:C,F:F,I:I,L:L").Select
Range("A1,C1,F1,I1,L1").EntireColumn.Select

With Selection
.NumberFormat = "General"
.Value = .Value

End With

End Sub

 

[pike]

hello,
You can tricky excel by adding zero

With Selection
.NumberFormat = "General"
.Value = .Value+0

End With

 

[Mallesh23]

Hi Pike,

Thanks for quick reply, just checked this, but still its not working getting run time error, 13 type mismatch,
please check from your end.

Regards,
Mallesh

 

[pike]

Hello is the data mixed

 

[pike]

Possibly blanks?

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

'ws.Range("A:A,C:C,F:F,I:I,L:L").Select
Range("A1,C1,F1,I1,L1").EntireColumn.Select

With Selection.SpecialCells(xlConstants)
.NumberFormat = "General"
.Value = .Value + 0
End With

 

[Mallesh23]

All columns are having number with apostrophe with its front side,

Regards,
Mallesh

 

[Peter_SSs]

See how this goes for you

Dim rA As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

For Each rA In Intersect(ws.UsedRange, ws.Range("A:A,C:C,F:F,I:I,L:L")).Areas
  With rA
    .NumberFormat = "General"
    .Value = .Value
  End With
Next rA

 

[pike]

Hello
The add zero to the value will strip the ' from in front of the number

the only entry that will cause an error will be a letter in one of the columns do you have headers?

 

[pike]

try just

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

'ws.Range("A:A,C:C,F:F,I:I,L:L").Select
Range("A1,C1,F1,I1,L1").EntireColumn.Select

With Selection.SpecialCells(2)
.NumberFormat = "General"
.Value = .Value
End With

 

[Peter_SSs]

@ pike
Are you testing the codes posted yourself? I ask because so far for me, they have all either thrown an error or altered the numbers in my worksheet.