Prev: Populate combobox with folder names
Next: finding matching text in different sheet
From: moonhk on 18 Feb 2010 22:53
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
 | 
Pages: 1
Prev: Populate combobox with folder names
Next: finding matching text in different sheet