Solved: Excel 2003 Insert Row between dates help needed

Discussion in 'Business Applications' started by Strybes, Jan 11, 2009.

Not open for further replies.

Joined:
Sep 7, 2007
Messages:
67
Hi All

Does anybody know if it is possible to insert rows between two dates so that the sequence follows.

Example:
I have the following 3 dates on 3 seperate lines.

1/02/2009
1/07/2009
1/07/2010

Is it possible to do a macro or write visual basic code to add rows so that it looks like this:

1/02/2009
1/03/2009
1/04/2009
1/05/2009
1/06/2009
1/07/2009
1/08/2009
1/09/2009
1/10/2009
1/11/2009
1/12/2009
1/01/2010
1/02/2010
1/03/2010
1/04/2010
1/05/2010
1/06/2010
1/07/2010

Thanks Strybes

2. turbodante

Joined:
Dec 19, 2008
Messages:
744
Most things to do with data manipulation is possible in Excel. The code below will do it for you if your dates are in Chronological order. The column (of your dates) can be changed by changing the variable 'CX'. HTH.

Code:
```Sub Macro1()
cx = "A"
Range(cx & "65536").End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select
RCT = Selection.Rows.Count
For x = RCT To 2 Step -1
stepmonth = DateSerial(Year(Cells(x - 1, cx)), (Month(Cells(x - 1, cx))), 1)
If DateSerial(Year(Cells(x, cx)), Month(Cells(x, cx)) - 1, 1) <> stepmonth Then
Rows(x).EntireRow.Insert
Cells(x, cx) = DateSerial(Year(Cells(x + 1, cx)), (Month(Cells(x + 1, cx)) - 1), 1)
x = x + 1
End If
Next
End Sub

```

Joined:
Sep 7, 2007
Messages:
67
turbodante

Thanks, that was exactly what I needed!
Only one problem though. The dates go for 55 years:

1/02/2009
1/07/2009
1/07/2010
.............
1/07/2059
1/07/2060
1/07/2061
1/07/2062

When I run your code, it selects all the dates but will only finish adding months until year 2059:

1/05/2059
1/06/2059
1/07/2059
1/07/2060
1/07/2061
1/07/2062

and comes up with a Run Time Error 13, Mismatch.

Thanks
Strybes

4. turbodante

Joined:
Dec 19, 2008
Messages:
744
I can not recreate this error with my sample data. Would you care to post up an example data set that gives you this error?

Joined:
Sep 7, 2007
Messages:
67
Hi turbodante

Thanks for your assistance, it is really appreciated.
I have attached a sample of data that I'm working with.
As you can see the data has opening and closing balances with the totals for transactions for the financial year.
Apart from having it include the months in between, I was then going to ask for help in how to do a caculation that would divide those transaction by the months in between the data (usually 12 except from the start date to the end of the 1st financial year). I was then going to have the opening balance = the previous months closing balance and have it sum up those monthly transactions to give me the next closing balance.
You know if it works as the data already entered should have the previous months closing balance equaling the same.
Is this possible.

Thanks
Strybes

File size:
63.5 KB
Views:
88
6. turbodante

Joined:
Dec 19, 2008
Messages:
744
I can not recreate the error which you had previously described, but have modified the code the do what you had original requested re: inserting missing months...

Code:
```Sub Macro1()

Dim CX As String
Dim topRow As Integer

CX = "B"
'Insert months
Range(CX & "65536").End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select
topRow = Selection.Row() + 2
RCT = Selection.Rows.Count
For x = RCT To topRow Step -1
stepmonth = DateSerial(Year(Cells(x - 1, CX)), (Month(Cells(x - 1, CX))), 1)
If DateSerial(Year(Cells(x, CX)), Month(Cells(x, CX)) - 1, 1) <> stepmonth Then
Rows(x).EntireRow.Insert
Cells(x, CX) = DateSerial(Year(Cells(x + 1, CX)), (Month(Cells(x + 1, CX)) - 1), 1)
x = x + 1
End If
Next
End Sub

```

7. turbodante

Joined:
Dec 19, 2008
Messages:
744

Joined:
Sep 7, 2007
Messages:
67
Thanks turbodante
I started another thread as this one was getting a bit lengthy and the macro you provided works great.

As Seen On