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