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 wrote:
>
> 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
>
> "Barb Reinhardt" <(E-Mail Removed)> wrote in message
> news:8420E17B-F715-400F-A970-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >>
--
Dave Peterson
|
Similar Threads
