object is not supporting for date extraction from cell

Hi Friends,

I want to extract date from the following series ( that I want to remove "_n1" and the date format would be (m/dd/yyyy). I wrote a procedure using worksheet function but range object is not supporting. Please let me know how to solve through procedure.



Exp. No.
07162009_n1
07162009_n1
07162009_n1
07162009_n1
07162009_n1
07162009_n1
07162009_n1
07162009_n1
07162009_n1
07162009_n1
07162009_n1
09252009_n1
07162009_n1
07162009_n1
07162009_n1
07162009_n1
09252009_n1
07162009_n1
07162009_n1
07162009_n1
07162009_n1

Sub datedemo()
Dim rng As Range
Dim i As Integer
Dim r As Integer
Set rng = Range("a1:a22")
r = 2
For i = 1 To rng.Rows.Count

rng.Cells(i).Offset(0, 1).Value = Application.WorksheetFunction.Date(Mid(rng.Cells(i), 5, 4), Left(rng.Cells(i), 2), Mid(rng.Cells(i), 3, 2))
Next i
End Sub

Hi Richard,

Thank you very much for replying my post.


Coming to the my post. I am getting (Type Mismatch) run time error.


I am using excel-2003, Is DateSerial function supports in excel 2003?



Thanks & Regards
Ramesh