Change Number to Text , Case error when cell is String and format isGeneral

[moonhk]

Hi All

How to check the cell is text or number ?
When Cell is string, Case Excel close and recoveing.

Sub FormatNum2str()
'~~ 2010/02/18
Dim iRows As Long
Dim iCols As Long
Dim ir, ic As Long
Dim iNum As Long
Dim strVal As String
If Application.Selection Is Nothing Then
MsgBox "No Open Worksheet", vbCritical
Exit Sub
End If

iRows = Application.Selection.Rows.Count
iCols = Application.Selection.Columns.Count


For ic = 1 To iCols
For ir = 1 To iRows
With Application.Selection.Item(ir, ic)
If .NumberFormatLocal = "General" Then
strVal = str(.Value)
.NumberFormatLocal = "@" '~~ Format as a text
.Value = strVal
End If
End With
Next ir
Next ic

End Sub

 

[OssieMac]

Change following line

strVal = Str(.Value)

to this
strVal = Format(.Value, "0")

You can use any valid number format between the double quotes.

 

[moonhk]

Change following line

strVal = Str(.Value)

to this
strVal = Format(.Value, "0")

You can use any valid number format between the double quotes.

--
Regards,

OssieMac










- 顯示被引用文字 -

Thank It works.