Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF statement inserting new rows
Good afternoon everyone
Hope someone can help me with this. I have a worksheet that is filled rows of information about transactions. The column A in the worksheet is the date the transaction occured. I would like to have a macro that notices when there is a change in date, and then inserts 3 rows below the final transction on the one date, and the new transctions of the next date. I know how to insert a single row using a macro: Selection.Insert Shift:=x1Down But I can't seem to figure out how to write the IF statement to compare the dates in Column A and insert 3 lines instead of just one. Would really appreciate the help Regards, PVANS |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF statement inserting new rows
See if this helps.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False Target.Resize(3, 1).EntireRow.Insert End If Application.EnableEvents = True End Sub "PVANS" wrote in message ... Good afternoon everyone Hope someone can help me with this. I have a worksheet that is filled rows of information about transactions. The column A in the worksheet is the date the transaction occured. I would like to have a macro that notices when there is a change in date, and then inserts 3 rows below the final transction on the one date, and the new transctions of the next date. I know how to insert a single row using a macro: Selection.Insert Shift:=x1Down But I can't seem to figure out how to write the IF statement to compare the dates in Column A and insert 3 lines instead of just one. Would really appreciate the help Regards, PVANS |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF statement inserting new rows
The code goes into the Sheet code module.
"PVANS" wrote in message ... Good afternoon everyone Hope someone can help me with this. I have a worksheet that is filled rows of information about transactions. The column A in the worksheet is the date the transaction occured. I would like to have a macro that notices when there is a change in date, and then inserts 3 rows below the final transction on the one date, and the new transctions of the next date. I know how to insert a single row using a macro: Selection.Insert Shift:=x1Down But I can't seem to figure out how to write the IF statement to compare the dates in Column A and insert 3 lines instead of just one. Would really appreciate the help Regards, PVANS |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF statement inserting new rows
I just noticed that the insert is before the changed cell, so here is a
modified code to make it after the changed cell. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False Target.Offset(1, 0).Resize(3, 1).EntireRow.Insert End If Application.EnableEvents = True End Sub "JLGWhiz" wrote in message ... The code goes into the Sheet code module. "PVANS" wrote in message ... Good afternoon everyone Hope someone can help me with this. I have a worksheet that is filled rows of information about transactions. The column A in the worksheet is the date the transaction occured. I would like to have a macro that notices when there is a change in date, and then inserts 3 rows below the final transction on the one date, and the new transctions of the next date. I know how to insert a single row using a macro: Selection.Insert Shift:=x1Down But I can't seem to figure out how to write the IF statement to compare the dates in Column A and insert 3 lines instead of just one. Would really appreciate the help Regards, PVANS |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF statement inserting new rows
Very basic method
Sub test() firstrow = 2'set to your first data row lastrow = 9 'set or caclulate lat data row datecolumn = 1'assign as necessary checkrow = firstrow While checkrow < lastrow If Cells(checkrow, datecolumn) < Cells(checkrow + 1, datecolumn) Then Rows(checkrow + 1).EntireRow.Insert Rows(checkrow + 1).EntireRow.Insert Rows(checkrow + 1).EntireRow.Insert checkrow = checkrow + 4 lastrow = lastrow + 3 Else: checkrow = checkrow + 1 End If Wend End Sub "PVANS" wrote: Good afternoon everyone Hope someone can help me with this. I have a worksheet that is filled rows of information about transactions. The column A in the worksheet is the date the transaction occured. I would like to have a macro that notices when there is a change in date, and then inserts 3 rows below the final transction on the one date, and the new transctions of the next date. I know how to insert a single row using a macro: Selection.Insert Shift:=x1Down But I can't seem to figure out how to write the IF statement to compare the dates in Column A and insert 3 lines instead of just one. Would really appreciate the help Regards, PVANS |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF statement inserting new rows
I'm not completely convinced this is what the OP is after; but, in case it
is, I have a couple of comments on your code. First, I think it would be a good idea to check if the Target is a single cell or not (otherwise multiple cell actions, delete perhaps, will affect unintended cells). You need to offset the target by one row otherwise the inserted cells will go on top, not below, it. A personal preference of mine is to minimize the checking for the target's applicability. Since the OP wants just Column A to be checked, there is no need to intersect the column with the target for that check... just check the target's column. Here is how I would do your code... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 7 And Target.Count = 1 Then Application.EnableEvents = False Target.Offset(1).EntireRow.Resize(3).Insert xlDown Application.EnableEvents = True End If End Sub -- Rick (MVP - Excel) "JLGWhiz" wrote in message ... See if this helps. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False Target.Resize(3, 1).EntireRow.Insert End If Application.EnableEvents = True End Sub "PVANS" wrote in message ... Good afternoon everyone Hope someone can help me with this. I have a worksheet that is filled rows of information about transactions. The column A in the worksheet is the date the transaction occured. I would like to have a macro that notices when there is a change in date, and then inserts 3 rows below the final transction on the one date, and the new transctions of the next date. I know how to insert a single row using a macro: Selection.Insert Shift:=x1Down But I can't seem to figure out how to write the IF statement to compare the dates in Column A and insert 3 lines instead of just one. Would really appreciate the help Regards, PVANS |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF statement inserting new rows
What did you mean when you said "below the final transaction on the one
date"? How is your data laid out... one date per sheet or and ordered column of dates? -- Rick (MVP - Excel) "PVANS" wrote in message ... Good afternoon everyone Hope someone can help me with this. I have a worksheet that is filled rows of information about transactions. The column A in the worksheet is the date the transaction occured. I would like to have a macro that notices when there is a change in date, and then inserts 3 rows below the final transction on the one date, and the new transctions of the next date. I know how to insert a single row using a macro: Selection.Insert Shift:=x1Down But I can't seem to figure out how to write the IF statement to compare the dates in Column A and insert 3 lines instead of just one. Would really appreciate the help Regards, PVANS |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF statement inserting new rows
Yes, I noticed that it was vague, but thought I would throw a template out
for the OP to work from. If it does not do what they want, they can make remarks and we can fix it. Some people just cannot put their requirements into a concise statement. "Rick Rothstein" wrote in message ... I'm not completely convinced this is what the OP is after; but, in case it is, I have a couple of comments on your code. First, I think it would be a good idea to check if the Target is a single cell or not (otherwise multiple cell actions, delete perhaps, will affect unintended cells). You need to offset the target by one row otherwise the inserted cells will go on top, not below, it. A personal preference of mine is to minimize the checking for the target's applicability. Since the OP wants just Column A to be checked, there is no need to intersect the column with the target for that check... just check the target's column. Here is how I would do your code... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 7 And Target.Count = 1 Then Application.EnableEvents = False Target.Offset(1).EntireRow.Resize(3).Insert xlDown Application.EnableEvents = True End If End Sub -- Rick (MVP - Excel) "JLGWhiz" wrote in message ... See if this helps. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False Target.Resize(3, 1).EntireRow.Insert End If Application.EnableEvents = True End Sub "PVANS" wrote in message ... Good afternoon everyone Hope someone can help me with this. I have a worksheet that is filled rows of information about transactions. The column A in the worksheet is the date the transaction occured. I would like to have a macro that notices when there is a change in date, and then inserts 3 rows below the final transction on the one date, and the new transctions of the next date. I know how to insert a single row using a macro: Selection.Insert Shift:=x1Down But I can't seem to figure out how to write the IF statement to compare the dates in Column A and insert 3 lines instead of just one. Would really appreciate the help Regards, PVANS |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF statement inserting new rows
Hi Nigel, Rick, JLGWhiz and Tim
I just wanted to say thank you for all the input. Tim's code has worked like an absolute charm, but I really do value all of your input. To answer your question Rick, the sheet is laid out with an ordered column of dates, and on each date there are one or more transcations. i.e: Date Units Price 29/07/09 20000 10 29/07/09 20000 10.5 30/07/09 10000 10.5 30/07/09 10000 10 etc. Tim's code worked very nicely inserting the lines Once again thank you to all of you Regards PVANS "Rick Rothstein" wrote: What did you mean when you said "below the final transaction on the one date"? How is your data laid out... one date per sheet or and ordered column of dates? -- Rick (MVP - Excel) "PVANS" wrote in message ... Good afternoon everyone Hope someone can help me with this. I have a worksheet that is filled rows of information about transactions. The column A in the worksheet is the date the transaction occured. I would like to have a macro that notices when there is a change in date, and then inserts 3 rows below the final transction on the one date, and the new transctions of the next date. I know how to insert a single row using a macro: Selection.Insert Shift:=x1Down But I can't seem to figure out how to write the IF statement to compare the dates in Column A and insert 3 lines instead of just one. Would really appreciate the help Regards, PVANS |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF statement inserting new rows
Hi Tim,
Hope you will read this, thanks so much for the code, worked really well. However, I have just noted that there is a second column that also needs to be differentiated in the worksheet. I tried to simply add a second If statement underneath the first one: firstrow = 2 'set to the first data row lastrow = 11 'set to the last data row datecolumn = 1 'assign as necessary servicecolumn = 8 'assign as necessary checkrow = firstrow While checkrow < lastrow If Cells(checkrow, datecolumn) < Cells(checkrow + 1, datecolumn) Then Rows(checkrow + 1).EntireRow.Insert Rows(checkrow + 1).EntireRow.Insert checkrow = checkrow + 3 lastrow = lastrow + 2 Else: checkrow = checkrow + 1 End If If Cells(checkrow, servicecolumn) < Cells(checkrow + 1, servicecolumn) Then Rows(checkrow + 1).EntireRow.Insert Rows(checkrow + 1).EntireRow.Insert checkrow = checkrow + 3 lastrow = lastrow + 2 Else: checkrow = checkrow + 1 End If Wend End Sub Of the four different transcation dates, this worked for three of them... it seperated by date, and then seperated by the second restriction. However, my fourth transaction (though seperated from the rest by date) still was grouped together in terms of the second restriction not seperated correctly.... Could you advise on how I could fix this please? Thanks for the help thus far Regards "Tim Rush" wrote: Very basic method Sub test() firstrow = 2'set to your first data row lastrow = 9 'set or caclulate lat data row datecolumn = 1'assign as necessary checkrow = firstrow While checkrow < lastrow If Cells(checkrow, datecolumn) < Cells(checkrow + 1, datecolumn) Then Rows(checkrow + 1).EntireRow.Insert Rows(checkrow + 1).EntireRow.Insert Rows(checkrow + 1).EntireRow.Insert checkrow = checkrow + 4 lastrow = lastrow + 3 Else: checkrow = checkrow + 1 End If Wend End Sub "PVANS" wrote: Good afternoon everyone Hope someone can help me with this. I have a worksheet that is filled rows of information about transactions. The column A in the worksheet is the date the transaction occured. I would like to have a macro that notices when there is a change in date, and then inserts 3 rows below the final transction on the one date, and the new transctions of the next date. I know how to insert a single row using a macro: Selection.Insert Shift:=x1Down But I can't seem to figure out how to write the IF statement to compare the dates in Column A and insert 3 lines instead of just one. Would really appreciate the help Regards, PVANS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
inserting rows inbetween rows of data ? | Excel Discussion (Misc queries) | |||
Copying & Inserting Rows w/o Affecting other Rows Etc. | Excel Worksheet Functions | |||
Inserting Blank rows after every row upto 2500 rows | Excel Worksheet Functions | |||
Why is inserting rows throwing off my hidden rows | Excel Programming | |||
Inserting multiple rows in excel with data in consecutive rows | Excel Programming |