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

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 Number to Text , Case error when cell is String and format
 

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
.

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

On 2月19日, 下�12時56分, OssieMac
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.