Macro Convert Text to Numbers Allow Type mismatch

[Rowland Hamilton]

Folks:

Found this Macro that works to Convert Text to Numbers:

Sub Enter_Values()
 For Each xCell In Selection
    xCell.Value = CDec(xCell.Value)
 Next xCell
End Sub

Problem is it "breaks" when it encounters text that can not be converted to a number:

Run-time error '13': Type mismatch

How do I allow for type mismatch?

Also tried this Macro that only worked once:

Sub ConvertToNumbers() 'won't work after first time
  Cells.SpecialCells(xlCellTypeLastCell) _
    .Offset(1, 1).Copy
  Selection.PasteSpecial Paste:=xlPasteValues, _
     Operation:=xlPasteSpecialOperationAdd
  With Selection
     .VerticalAlignment = xlTop
     .WrapText = False
  End With
  Selection.EntireColumn.AutoFit
End Sub

Thank you - Rowland
Macro Convert Text to Numbers Allow Type mismatch

 

[taurean]

Tell your program to ignore the error and go to the next line:

Sub Enter_Values()
[COLOR=blue]On Error Resume Next[/COLOR]
 For Each xCell In Selection
    xCell.Value = CDec(xCell.Value)
Next xCell
End Sub

Edit: Please use VoG's routine as it resets error handling!

 

[VoG]

Try

Sub Enter_Values()
On Error Resume Next
 For Each xCell In Selection
    xCell.Value = CDec(xCell.Value)
 Next xCell
 On Error GoTo 0
End Sub

 

[Rowland Hamilton]

Folks:

I think I found my answer. Of the 3 Macros I found that Convert Text to Numbers, this one allows for type mismatch and just leaves the offending mismatch as is (perfect).

Sub greenbananna()
Dim cell As Range
For Each cell In Selection
cell.Value = CStr(cell.Value)
Next cell
End Sub[CODE]
 
This one Converts Text to Numbers but "breaks" when has Type mismatch (Run-time error '13':)
 
[CODE]Sub Enter_Values()
 For Each xCell In Selection
    xCell.Value = CDec(xCell.Value)
 Next xCell
End Sub
[COLOR=#0070c0] 
[COLOR=#000000]This one only worked the first time I tried it so very unreliable:[/COLOR]
 
[/COLOR][CODE]Sub ConvertToNumbers() 'won't work after first time
  Cells.SpecialCells(xlCellTypeLastCell) _
    .Offset(1, 1).Copy
  Selection.PasteSpecial Paste:=xlPasteValues, _
     Operation:=xlPasteSpecialOperationAdd
  With Selection
     .VerticalAlignment = xlTop
     .WrapText = False
  End With
  Selection.EntireColumn.AutoFit
End Sub

Thank you - Rowland
Macro Convert Text to Numbers Allow Type mismatch

 

[shg]

Another way:

Sub RH()
    Dim cell As Range
 
    For Each cell In Intersect(Selection.Cells, ActiveSheet.UsedRange)
        With cell
            If Not .HasFormula And _
               VarType(.Value) = vbString _
               And IsNumeric(.Value) Then
                .Value = CDbl(.Value)
            End If
        End With
    Next cell
End Sub

 

[Rowland Hamilton]

Follow-up:

This Macro doesn't need Dim xCell As Range but when I put the code into 6 other Macros
and one of them needed it.

Sub Enter_Values()
Dim xCell as Range 'only needed when embedded in some macros
 On Error Resume Next
 For Each xCell In Selection
    xCell.Value = CDec(xCell.Value)
 Next xCell
End Sub

Previous message:
Sub greenbanana() Just tested it with my actual data and it didn't work like I wanted it to. It left a lot of numbers unconverted with little green message triangles in them that say "this numbers is formatted as text preceded by an apostrophe."


Sub Enter_Values() works better. It turns blanks into zeros, but If had to worry about that I probably just need to add an if statement to keep the blanks.

Thanks - Rowland

 

[mikerickson]

The Val function is very useful
Val("smith") = 0
Val("1e+2") = 100
Val("12.87") = 12.87
Val("1.2.3") = 1.2

it even handles the old school, Val("2D-3") = 0.002