number formatting

[Guest]

Get message next to cell, formatted text or apostrophe.
If I use format, cell, number, number: it doesn't always solve problem.
If I use the message, "convert to number" it always works.
Want macro that I can use on a selected range of cells that performs the
conversion using that process.
THX

 

[Guest]

Hi Scottie -

If all cells in the selection are non-empty and contain only numeric
characters (with or without the leading apostrophe), Scottie01 will work
properly. If your selection contains some empty cells or some cells with
alpha characters, use Scottie02 (it won't convert empty cells and alpha cells
to "0" as will Scottie01). Both are included for demonstration purposes, but
I'd suggest Scottie02 because it's more universal.

Sub Scottie01()
For Each itm In Selection
itm.Value = Val(itm)
Next 'itm
End Sub

Sub Scottie02()
For Each itm In Selection
On Error Resume Next
If itm <> "" Then itm.Value = CSng(itm.Value)
Next 'itm
End Sub