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

IF statement inserting new rows
 

Sub InsertRows()
Dim lLR As Long, lCR As Long
With Sheets("Sheet1")
lLR = .Cells(Rows.Count, "A").End(xlUp).Row
For lCR = lLR To 1 Step -1
If lCR 1 And lCR < lLR Then
If .Cells(lCR, "A") < .Cells(lCR - 1, "A") Then
Rows(lCR & ":" & lCR + 2).Insert Shift:=xlDown
End If
End If
Next
End With
End Sub


--

Regards,
Nigel




"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

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

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

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

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

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

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

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

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

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