I am using the following code to decompose the date out of a textua
description. However, when it finds the value "1058 8" in the text, i
says that it can be coerced to a date, but then when I use CDate t
coerce it, I get an application error. Any thoughts, anyone? Is thi
a known problem?

Here is my code:

Sub PullOutDate()
For Each x In Selection.Cells
l = Len(x.Value)
For y = 1 To l - 8
If IsDate(Mid(x.Value, y, 6)) Then
x.Cells(1, 2).Value = CDate(Mid(x.Value, y, 6))
ElseIf IsDate(Mid(x.Value, y, 7)) Then
x.Cells(1, 2).Value = CDate(Mid(x.Value, y, 7))
ElseIf IsDate(Mid(x.Value, y, 8)) Then
x.Cells(1, 2).Value = CDate(Mid(x.Value, y, 8))
End If
Next y
Next x
End Sub

Thanks for your help!

Tom Stoc

--
tkstoc
-----------------------------------------------------------------------
tkstock's Profile: http://www.excelforum.com/member.php...fo&userid=1444
View this thread: http://www.excelforum.com/showthread.php?threadid=27419

Tom,

The value you specify converts to a date for me: 08/01/1058 ...
'----------------------------
Sub TestForDate()
Dim strDate As String
Dim x As Variant
Dim y As Variant
Dim z As Variant

strDate = "1058 8"
x = IsDate(strDate)
y = CDate(strDate)
z = DateValue(strDate)
MsgBox "Isdate = " & x & ", Cdate is " & y & ", DateValue is " & z
End Sub
'----------------------------

Possibly your windows date settings are out of whack? or
You could try adding a $ after all the Mid functions "Mid$" to make
sure that you are returning a string - but I doubt that is the problem.

Regards,
Jim Cone
San Francisco, CA

"tkstock" wrote in message ...

I am using the following code to decompose the date out of a textual
description. However, when it finds the value "1058 8" in the text, it
says that it can be coerced to a date, but then when I use CDate to
coerce it, I get an application error. Any thoughts, anyone? Is this
a known problem?

Here is my code:

Sub PullOutDate()
For Each x In Selection.Cells
l = Len(x.Value)
For y = 1 To l - 8
If IsDate(Mid(x.Value, y, 6)) Then
x.Cells(1, 2).Value = CDate(Mid(x.Value, y, 6))
ElseIf IsDate(Mid(x.Value, y, 7)) Then
x.Cells(1, 2).Value = CDate(Mid(x.Value, y, 7))
ElseIf IsDate(Mid(x.Value, y, 8)) Then
x.Cells(1, 2).Value = CDate(Mid(x.Value, y, 8))
End If
Next y
Next x
End Sub

Thanks for your help!

Tom Stock


--
tkstock
------------------------------------------------------------------------
tkstock's Profile: http://www.excelforum.com/member.php...o&userid=14443
View this thread: http://www.excelforum.com/showthread...hreadid=274190