IF statement inserting new rows

[PVANS]

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

 

[JLGWhiz]

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

 

[JLGWhiz]

The code goes into the Sheet code module.

 

[JLGWhiz]

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

 

[Tim Rush]

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

 

[Rick Rothstein]

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 Rothstein]

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?

 

[JLGWhiz]

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.

 

[PVANS]

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

 

[PVANS]

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