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

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


"moonhk" wrote:

> 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
> .
>

On 2月19日, 下�12時56分, OssieMac <Ossie...@discussions.microsoft.com>
wrote:
> 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
>
>
>
> "moonhk" wrote:
> > 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
> > .- éš±è—�被引用文å*— -
>
> - 顯示被引用文å*— -

Thank It works.