How to prevent change Numeric value to date format ?
On 12$B7n(B7$BF|(B, $Be8a(B11$B;~(B28$BJ,(B, Joel wrote:
You have to understand what date format actually is. It is a number. Date
is the number of days from Jan 1, 1900. So the date is around 39,000. A
date will pass a numeric test.
You may be able to test for the date being a range from a start date to an
end date such as
mydate datevalue("1/1/2000") and mydate < datevalue("12/12/2010")
"moonhk" wrote:
Hi Reader
How to prevent change Numeric value to date format ?
Add Not IsNumeric(Selection.Item(ir, ic).Value) not work.
Sub ChangeYYYYMMDD()
Dim iRows As Long
Dim iColumns As Long
Dim ir As Long
Dim ic As Long
iRows = Selection.Rows.Count
iColumns = Selection.Columns.Count
For ir = 1 To iRows
For ic = 1 To iColumns
If IsDate(Selection.Item(ir, ic).Value) And Not
IsNumeric(Selection.Item(ir, ic).Value) Then
Selection.NumberFormatLocal = "yyyy/mm/dd"
End If
Next ic
Next ir
End Sub- $Bp,i6Ho0zMQJ8;z(B -
- $Bp}<(Ho0zMQJ8;z(B -
Thank. I just using VarType to test the cell type.
Sub ChangeYYYYMMDD()
'~~ 2007/12/07 moonhk
Dim iRows As Long
Dim iColumns As Long
Dim ir As Long
Dim ic As Long
Dim k
iRows = Selection.Rows.Count
iColumns = Selection.Columns.Count
For ir = 1 To iRows
For ic = 1 To iColumns
If VarType(Selection.Item(ir, ic)) = vbDate Then
Selection.Item(ir, ic).NumberFormatLocal = "yyyy/mm/dd"
End If
Next ic
Next ir
End Sub
|