|
Creating Intermediate Date Values - New Problem
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 |
Creating Intermediate Date Values - New Problem
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 |
Creating Intermediate Date Values - New Problem
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 "Barb Reinhardt" wrote in message ... 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 |
| All times are GMT +1. The time now is 07:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com