PDA

View Full Version : code for inserting & Copying rows



nat1
10-22-2007, 12:21 PM
Hi,

I'm new to the forum (recommended by MrExcel.com), relatively new to vba and consider myself a novice.

I'm trying to insert 3 or 2 new rows above the data range when certain conditions are true, then insert values into these rows. Then I need to copy the data from col A:G immediately below the new rows into these rows.

The code I have so far............Sub Maintenance_Concept1()
Dim LR As Long
Dim x As Long
LR = Range("H" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For x = LR To 1 Step -1
Select Case Range("Q" & x)
Case "1"
Range("Q" & x).Offset(0, 0).EntireRow.Insert: _
Range("I" & x).Offset(0, 0).Value = "ORGANIZATIONAL LEVEL-SCHEDULED MAINTENANCE:": _
Range("Q" & x).Offset(0, 0).EntireRow.Insert: _
Range("Q" & x).Offset(0, 0).EntireRow.Insert: _
Range("I" & x).Offset(0, 0).Value = "SYSTEM:"
Case "2"
Range("Q" & x).Offset(0, 0).EntireRow.Insert: _
Range("I" & x).Offset(0, 0).Value = "INTERMEDIATE LEVEL - SCHEDULED MAINTENANCE:": _
Range("Q" & x).Offset(0, 0).EntireRow.Insert: _
Range("Q" & x).Offset(0, 0).EntireRow.Insert
Case "3"
Range("Q" & x).Offset(0, 0).EntireRow.Insert: _
Range("I" & x).Offset(0, 0).Value = "DEPOT LEVEL - SCHEDULED MAINTENANCE:": _
Range("Q" & x).Offset(0, 0).EntireRow.Insert: _
Range("Q" & x).Offset(0, 0).EntireRow.Insert
Case "4", "5", "6"
Range("Q" & x).Offset(0, 0).EntireRow.Insert: _
Range("I" & x).Offset(0, 0).Value = "UNSCHEDULED MAINTENANCE:": _
Range("Q" & x).Offset(0, 0).EntireRow.Insert: _
Range("Q" & x).Offset(0, 0).EntireRow.Insert
End Select
Next x
End Sub
Hope this makes sense!!!! Thank-you in advance for any help/advice you can give me.

Bob Phillips
10-22-2007, 12:33 PM
Who at MrExcel suggested that, and why? Not saying they aren't right, but it is not like them.



Sub Maintenance_Concept1()
Dim LR As Long
Dim x As Long
LR = Range("H" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For x = LR To 1 Step -1
Select Case Range("Q" & x)
Case "1"
Rows(x).Insert
Range("I" & x).Value = "ORGANIZATIONAL LEVEL-SCHEDULED MAINTENANCE:"
Rows(x).Resize(2).Insert
Range("I" & x).Value = "SYSTEM:"
Cells(x + 3, "A").Resize(, 7).Copy Range("A" & x).Resize(3)
Case "2"
Rows(x).Insert
Range("I" & x).Value = "INTERMEDIATE LEVEL - SCHEDULED MAINTENANCE:"
Rows(x).Resize(2).Insert
Cells(x + 3, "A").Resize(, 7).Copy Range("A" & x).Resize(3)
Case "3"
Rows(x).Insert
Range("I" & x).Value = "DEPOT LEVEL - SCHEDULED MAINTENANCE:"
Rows(x).Resize(2).Insert
Cells(x + 3, "A").Resize(, 7).Copy Range("A" & x).Resize(3)
Case "4", "5", "6"
Rows(x).Insert
Range("I" & x).Value = "UNSCHEDULED MAINTENANCE:"
Rows(x).Resize(2).Insert
Cells(x + 3, "A").Resize(, 7).Copy Range("A" & x).Resize(3)
End Select
Next x
End Sub

nat1
10-22-2007, 12:48 PM
Hi,
Please except my opologies for any confusion, what I should have said was I followed their recommended links:doh:

Thank-you very much for your help, I will try the code and let you know how I get on.

nat1
10-22-2007, 01:14 PM
xld,

Thank-you so much for the code it works fine. One other question; how easy would it be to perform a similar procedure if certains conditions specified in the select case don't exist?

Thanks

Bob Phillips
10-22-2007, 01:16 PM
Just add a Case Else to the code and some action code for that.

nat1
10-22-2007, 01:22 PM
Ok thank-you once again for your help, it is appreciated.

Regards,

nat1
10-24-2007, 11:25 AM
Hello again,

I am still having problems with this vba procedure. I have revised the code because originally I was using an if formula in col Q for the select case. I have now coded this into the procedure.
Sub Maintenance_Concept2()
Dim LR As Long
Dim x As Long
LR = Range("H" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For x = LR To 12 Step -1
If Cells(x, 1) <> Cells(x - 1, 1) And Cells(x, 7) = "SCHEDULED" _
And Cells(x, 8) = "1ST LINE" Then
Rows(x).Insert
Range("I" & x).Value = "ORGANIZATIONAL LEVEL-SCHEDULED MAINTENANCE:"
Rows(x).Resize(2).Insert
Range("I" & x).Value = "SYSTEM:"
Cells(x + 3, "A").Resize(, 8).Copy Range("A" & x).Resize(3)
ElseIf Cells(x - 1, 8) <> Cells(x, 8) And Cells(x, 7) = "SCHEDULED" _
And Cells(x, 8) = "2ND LINE" Or Cells(x, 1) <> Cells(x - 1, 8) And Cells(x, 7) = "SCHEDULED" _
And Cells(x, 8) = "2ND LINE" Then
Rows(x).Insert
Range("I" & x).Value = "INTERMEDIATE LEVEL - SCHEDULED MAINTENANCE:"
Rows(x).Resize(2).Insert
Cells(x + 3, "A").Resize(, 8).Copy Range("A" & x).Resize(3)
ElseIf Cells(x - 1, 8) <> Cells(x, 8) And Cells(x, 7) = "SCHEDULED" _
And Cells(x, 8) = "3/4TH LINE" Or Cells(x, 1) <> Cells(x - 1, 1) And Cells(x, 7) = "SCHEDULED" _
And Cells(x, 8) = "3/4TH LINE" Then
Rows(x).Insert
Range("I" & x).Value = "DEPOT LEVEL - SCHEDULED MAINTENANCE:"
Rows(x).Resize(2).Insert
Cells(x + 3, "A").Resize(, 8).Copy Range("A" & x).Resize(3)
ElseIf Cells(x, 7) <> Cells(x - 1, 7) And Cells(x, 7) = "UNSCHEDULED" _
And Cells(x, 8) = "1ST LINE" Or Cells(x - 1, 1) <> Cells(x, 1) And Cells(x, 7) = "UNSCHEDULED" _
And Cells(x, 8) = "1ST LINE" Or Cells(x - 1, 8) <> Cells(x, 8) And Cells(x, 7) = "UNSCHEDULED" _
And Cells(x, 8) = "2ND LINE" Or Cells(x - 1, 8) <> Cells(x, 8) And Cells(x, 7) = "UNSCHEDULED" _
And Cells(x, 8) = "3/4TH LINE" Then
Rows(x).Insert
Range("I" & x).Value = "UNSCHEDULED MAINTENANCE:"
Rows(x).Resize(1).Insert
Cells(x + 2, "A").Resize(, 8).Copy Range("A" & x).Resize(2)
End If
Next
For x = LR To 12 Step -1
If Cells(x - 1, 1) <> Cells(x, 1) And Cells(x - 1, 7) <> Cells(x, 7) And _
Cells(x, 7) = "SCHEDULED" And Cells(x, 8) = "1ST LINE" Or Cells(x - 1, 7) <> Cells(x, 7) And _
Cells(x, 7) = "SCHEDULED" And Cells(x, 8) = "1ST LINE" Then
Rows(x).Resize(17).Insert
End If
Next
End Sub
The procedure works upto the second For statement, but now I'm stuck again!! All of the values are correctly inserted but my problem now is figuring out how to insert rows where any of the maintenance levels don't exist.

For example, V62 has only one level of maintenance (scheduled 1st line) but I still need the other 2 levels (intermediate/depot) inserted with the value of none.

I have attached an .xls file the first sheet shows the source, the second part of the expected result.

If someone would be so kind to help me out I would be extremely greatfull. I have spent alot of time on this now and I don't seem to be making significant progress:help

Bob Phillips
10-24-2007, 11:37 AM
Surely that cannot work, it all references ranges on the same sheet.

But the second for, perhaps it should be



For x = LR To 12 Step -1
If (Cells(x - 1, 1) <> Cells(x, 1) And Cells(x - 1, 7) <> Cells(x, 7) And _
Cells(x, 7) = "SCHEDULED" And Cells(x, 8) = "1ST LINE") Or _
(Cells(x - 1, 7) <> Cells(x, 7) And _
Cells(x, 7) = "SCHEDULED" And Cells(x, 8) = "1ST LINE") Then
Rows(x).Resize(17).Insert
End If
Next

nat1
10-24-2007, 12:31 PM
xld,

The second sheet is purely to show what the result should look like after the procedure is run.

Thank-you for the revised for code.

nat1
10-24-2007, 12:45 PM
xld,

The code works but the rows are inserted above the range and I need them below. I'm not sure how to go about doing this. Would I have to select the range below first then insert?

Thanks

Bob Phillips
10-24-2007, 01:49 PM
For x = LR To 12 Step -1
If (Cells(x - 1, 1) <> Cells(x, 1) And Cells(x - 1, 7) <> Cells(x, 7) And _
Cells(x, 7) = "SCHEDULED" And Cells(x, 8) = "1ST LINE") Or _
(Cells(x - 1, 7) <> Cells(x, 7) And _
Cells(x, 7) = "SCHEDULED" And Cells(x, 8) = "1ST LINE") Then
Rows(x-1).Resize(17).Insert
End If
Next


perhaps?

nat1
10-24-2007, 03:59 PM
xld,


Rows(x-1).Resize(17).Insert did not work so I worked on it some more and eventually had this code working......
For x = LR To 12 Step -1
If Cells(x + 1, 1) <> Cells(x, 1) And Cells(x + 1, 7) <> Cells(x, 7) And _
Cells(x, 7) = "SCHEDULED" And Cells(x, 8) = "1ST LINE" Then
Rows(x + 1).Resize(17).Insert
Cells(x, "A").Resize(, 8).Copy Range("A" & x).Resize(18)
Range("I" & x + 2).Value = "UNSCHEDULED MAINTENANCE:"
Range("I" & x + 3).Value = "NONE."
Range("I" & x + 6).Value = "INTERMEDIATE LEVEL - SCHEDULED MAINTENANCE:"
Range("I" & x + 7).Value = "NONE."
Range("I" & x + 9).Value = "UNSCHEDULED MAINTENANCE:"
Range("I" & x + 10).Value = "NONE."
Range("I" & x + 13).Value = "DEPOT LEVEL - SCHEDULED MAINTENANCE:"
Range("I" & x + 14).Value = "NONE."
Range("I" & x + 16).Value = "UNSCHEDULED MAINTENANCE:"
Range("I" & x + 17).Value = "NONE."
ElseIf Cells(x + 1, 1) <> Cells(x, 1) And Cells(x, 7) = "SCHEDULED" And Cells(x, 8) = "1ST LINE" Then
Rows(x + 1).Resize(17).Insert
Cells(x, "A").Resize(, 8).Copy Range("A" & x).Resize(18)
Range("I" & x + 2).Value = "UNSCHEDULED MAINTENANCE:"
Range("I" & x + 3).Value = "NONE."
Range("I" & x + 6).Value = "INTERMEDIATE LEVEL - SCHEDULED MAINTENANCE:"
Range("I" & x + 7).Value = "NONE."
Range("I" & x + 9).Value = "UNSCHEDULED MAINTENANCE:"
Range("I" & x + 10).Value = "NONE."
Range("I" & x + 13).Value = "DEPOT LEVEL - SCHEDULED MAINTENANCE:"
Range("I" & x + 14).Value = "NONE."
Range("I" & x + 16).Value = "UNSCHEDULED MAINTENANCE:"
Range("I" & x + 17).Value = "NONE."
End If
Next
There is still some way to go as there are more conditions but I am getting there!!!

Thank-you very much for your help.

nat1
10-26-2007, 11:41 AM
Hello again,

Right I have to say that I'm completely stuck now:help I just cannot figure out how I can find which maintenance levels don't exist and then insert the applicable values?

Maybe I could use the data from sheet2 to create some variables to store this data in for use within the procedure?.

Any suggestions/advice anybody can give me, would be very welcome as I'm pulling my hair out over this project!