Because you're importing the data from Access, the dates are probably
being treated as text. Add the CDate function to convert the text to
dates. I've also added a line to clear sheet2 before running the rest of
the code:

'=========================
Sub TransformDates()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim c As Range
Dim i As Integer
Dim r As Long
Dim r2 As Long

Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
r = ws.Cells(Rows.Count, 1).End(xlUp).Row

ws2.Cells.Clear
ws2.Cells(1, 1).Value = "Person"
ws2.Cells(1, 2).Value = "Event"
ws2.Cells(1, 3).Value = "Date"

For Each c In ws.Range(ws.Cells(2, 1), ws.Cells(r, 1))
i = CDate(c.Offset(0, 3)) - CDate(c.Offset(0, 2)) + 1
For i = 1 To i
r2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
c.Resize(1, 2).Copy Destination:=ws2.Cells(r2, 1)
ws2.Cells(r2, 3).Value = CDate(c.Offset(0, 2)) + i - 1
Next i
Next c
End Sub
'============================

Tracy H wrote:
Debra,

Thanks for prompt reply!

When I run this macro (from the Tools Menu), I get an error 'Runtime error(13) type mismatch' and when I click debug, it highlights this line:
i = c.Offset(0, 3) - c.Offset(0, 2) + 1

I suspect I am just doing something wrong...I would greatly appreciate any suggestions.

Tracy


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html