Date String To Serial
On Nov 8, 7:45*am, Ron Rosenfeld wrote:
On Sat, 8 Nov 2008 06:13:43 -0800 (PST), goss wrote:
On Nov 7, 8:17*pm, Ron Rosenfeld wrote:
On Fri, 7 Nov 2008 12:38:44 -0800 (PST), goss wrote:
Thanks Ron,
Option Explicit is on
I was not sure of data type for s?
I tried S as string and long
Neither worked
I receive A Typ Mismatch Error Message
Thanks!
goss
s is String.
As in:
======================
Option Explicit
Sub foo()
Dim s As String
Dim MyRange As Range, c As Range
Set MyRange = Range("a1") 'obviously should be changed.
For Each c In MyRange
* * s = Replace(c.Value, "'", ", 20")
* * c.Value = DateValue(s) + TimeValue(s)
* * c.NumberFormat = "mmm dd, yyyy h:mm am/pm"
Next c
End Sub
=======================
--ron
Thanks Ron,
My extract code had a handler so if the date was not found a zero was
returned
Once the date is found, fill down to all records
So rows 1 and 2 had zeroes, all other rows had the date string
I added a test for If Not IsNumeric(C.Value) and now the code works
great!
Thanks!
goss
Snippet:
* *'Replace
* * * *For Each C In myRange
* * * * * *If Not IsNumeric(C.Value) Then
* * * * * * * *s = Replace(C.Value, "'", ", 20")
* * * * * * * *C.Value = DateValue(s) + TimeValue(s)
* * * * * * * *C.NumberFormat = "MM/DD/YYYY"
* * * * * *End If
* * * *Next C
Glad to see you got it working.
Although if you have a zero in a cell, IsNumeric should test TRUE.
Another approach would be to add an error handler, with an appropriate message
depending on the error.
--ron- Hide quoted text -
- Show quoted text -
Thanks Ron,
The test does return TRUE for the first 2 rows, so the zeroes are not
formatted and no type mismatch error is received
The code then executes as expected for all remaining rows
Thanks!
goss
|