Creating Intermediate Date Values - New Problem

[Jim Berglund]

I'm using the following code (provided by Joel, with my great appreciation)
to create a set of prorated dates between two dates for a series of dates.
eg
Column L
9/3/1999
..
..
..
..
..
..
..
10/6/2004
4/2/1996
..
..
..
4/30/2007
etc.

I. tried to use it on another similar worksheet but ran into a type mismatch
error 13 problem after it had run through the first set successfully. I have
tried to correct it withot success.

Any ideas will be appreciated...

Thanks
Jim Berglund


Sub Prorate()

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

First = True
For RowCount = 2 To LastRow
If IsEmpty(Cells(RowCount, "A")) Then 'if last row was empty use todays
date to prorate
If IsEmpty(Cells(RowCount - 1, "L")) Then 'Use last NewDate to
prorate
OldDate = NewDate
NewDate = Now()
DeltaDate = (NewDate - OldDate) / _
(RowCount - OldRow)
'fill in prorated dates
For RowCount2 = OldRow To (RowCount - 1)

MyDate = Cells(RowCount2 - 1, "L") + _
DeltaDate
Cells(RowCount2, "L") = MyDate
Next RowCount2

End If
First = True
Else
If First = True Then

OldDate = Cells(RowCount, "L")
OldRow = RowCount
First = False
Else
If Not IsEmpty(Cells(RowCount, "L")) Then
NewDate = Cells(RowCount, "L")
DeltaDate = (NewDate - OldDate) / (RowCount - OldRow)
'fill in prorated dates
For RowCount2 = (OldRow + 1) To (RowCount - 1)

MyDate = Cells(RowCount2 - 1, "L") + _
DeltaDate
Cells(RowCount2, "L") = MyDate
Next RowCount2
OldDate = NewDate
OldRow = RowCount
End If
End If
End If

Next RowCount



End Sub

 

[Guest]

Jim,

Try stepping through the code and tell us where the code has an error and
what the values might be.

Thanks,
Barb

 

[Jim Berglund]

It occurs in the row surrounded with asterisks. It completes from the first
variable (9/3/1999) to the second(10/6/2004) and then I get the error

Thanks for your interest and help.
Jim

 

[Dave Peterson]

If you have asterisks in the cell with the dates, then that cell isn't a date
anymore--it's just plain old text. And excel (and VBA) doesn't like to do
arithmetic with plain text.

Can you put the asterisks in a separate column. It would make doing arithmetic
much easier (in excel and VBA).

 

[Jim Berglund]

Barb, I created a note in Outlook that has screenshots and the actual files
I'm using. Can I please send it to you?

Jim Berglund