Creating Intermediate Date Values - New Problem

  • Thread starter Thread starter Jim Berglund
  • Start date Start date
J

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
 
Jim,

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

Thanks,
Barb
 
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
 
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).
 
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
 
Back
Top