Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change a 1,2,3 or 4 digit number to a 6 character text string | Excel Worksheet Functions | |||
Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
change a string of numbers in to number format | Excel Worksheet Functions | |||
Change the text from lower case to upper case in an Excel work boo | Excel Discussion (Misc queries) | |||
Excel should let me change case in the cell the text is in. | Excel Worksheet Functions |