Going round in Loops

S

SGT_Spittal

New Member
Joined
Dec 10, 2015
Messages
10
Good Afternoon,

I am struggling with my code.

I download a spread sheet from a DB that I need to manipulate by adding a number of rows depending on some criteria

my problem is the initial number of rows for the loop increases with each pass

Code: 
Sub Test3()
      Dim x As Integer
      Dim y As Integer
      Dim AM As Integer
      Dim FM As Integer
      Dim MM As Integer
      Dim rng As Range
      
     
      AM = 5
      FM = 1
      MM = 3
     
      ' Set numrows = number of rows of data.
      NumRows = Range("O1", Range("O1").End(xlDown)).Rows.Count
      
      ' Select cell O1.
      Range("O1").Select
      ' Establish "For" loop to loop "numrows" number of times.
     
     
      For x = 1 To NumRows
    
         If ActiveCell.Value = "-" Then
            Set rng = ActiveCell
            If ActiveCell.Offset(0, -9).Value = "MM" Then
       For y = 1 To MM
        ActiveCell.EntireRow.Select
       
    
        '~~> Copy the range
        Selection.Copy
 
        '~~> Insert the range
       
        Selection.Insert Shift:=xlDown
        Next
 
        '~~> Clear the clipboard. More importantly remove the
        '~~ ant like borders
        Application.CutCopyMode = False
   
    Range(rng.Address).Offset(-3).Select
            ActiveCell.Value = "03DZ - BZB"
            ActiveCell.Offset(1).Value = "04DZ - BZB"
            ActiveCell.Offset(2).Value = "09DZ - BZE"
            ActiveCell.Offset(3).Value = "12DZ - BZH"
 
      
                      
       
          
           
        End If
        
   
   
    End If
    ActiveCell.Offset(1, 0).Select
 
    Next
      
    
   End Sub
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
try the below

Code: 
Sub test()
Dim lr As Long, i As Long
lr = Range("O" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
If Range("O" & i) = "-" And Range("O" & i).Offset(0, -9).Value = "MM" Then
Range("O" & i).EntireRow.Insert shift:=xlShiftDown
Range("O" & i).EntireRow.Insert shift:=xlShiftDown
Range("O" & i).EntireRow.Insert shift:=xlShiftDown
 Range("O" & i).Value = "03DZ - BZB"
           Range("O" & i).Offset(1).Value = "04DZ - BZB"
            Range("O" & i).Offset(2).Value = "09DZ - BZE"
            Range("O" & i).Offset(3).Value = "12DZ - BZH"
            End If
            Next
End Sub
 
Upvote 0
Thank you very much this worked great.

taking your code I have utilised it 4 times for different variables as shown below. is there anyway that I could reduce this

Code: 
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Sub MMADD()<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Dim lr As Long, i As Long<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]lr = Range("O" & Rows.Count).End(xlUp).Row<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]For i = lr To 1 Step -1<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]If Range("O" & i).Value = "-" AndRange("O" & i).Offset(0, -9).Value = "MM" AndRange("O" & i).Offset(0, -5).Value = "ImpactAssessment" and Range("O" & i).Offset(0, -4).Value ="PENDING"  Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insert shift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]           Range("O" & i).Value = "03DZ - BZB"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]           Range("O" & i).Offset(1).Value = "04DZ - BZB"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]           Range("O" & i).Offset(2).Value = "09DZ - BZE"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]           Range("O" & i).Offset(3).Value = "12DZ - BZH"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]           Range("O" & i).Offset(4).Value = "***"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]            End If<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]            Next<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]End Sub<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Sub FMADD()<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Dim lr As Long, i As Long<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]lr = Range("O" & Rows.Count).End(xlUp).Row<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]For i = lr To 1 Step -1<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]If Range("O" & i).Value = "-" AndRange("O" & i).Offset(0, -9).Value = "FM" AndRange("O" & i).Offset(0, -5).Value = "ImpactAssessment" and Range("O" & i).Offset(0, -4).Value ="PENDING" Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]           Range("O" & i).Value = "01DZ - BZA"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]           Range("O" & i).Offset(1).Value = "02DZ - BZA"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]           Range("O" & i).Offset(2).Value = "***"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]           End If<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]            Next<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]End Sub<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Sub AMADD()<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Dim lr As Long, i As Long<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]lr = Range("O" & Rows.Count).End(xlUp).Row<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]For i = lr To 1 Step -1<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]If Range("O" & i).Value = "-" AndRange("O" & i).Offset(0, -9).Value = "AM" AndRange("O" & i).Offset(0, -5).Value = "ImpactAssessment"  and Range("O"& i).Offset(0, -4).Value = "PENDING" Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]           Range("O" & i).Value = "05DZ - BZC"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]           Range("O" & i).Offset(1).Value = "06DZ - BZC"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).Offset(2).Value = "07DZ -BZD"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).Offset(3).Value = "08DZ -BZD"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).Offset(4).Value = "10DZ -BZF"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).Offset(5).Value = "11DZ -BZG"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).Offset(6).Value ="***"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]           End If<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]            Next<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]End Sub<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Sub HOURs()<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Dim Ar As Long, x As Long<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Ar = Range("O" & Rows.Count).End(xlUp).Row<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]For x = Ar To 1 Step -1<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]If Range("O" & x) <> "***" AndRange("K" & x).Value = "PENDING" Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & x).Offset(0, 2).Value ="0"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & x).Offset(0, 6).Value ="0"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]End If<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Next<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]End Sub<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Sub NMADD()<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Dim lr As Long, i As Long<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]lr = Range("O" & Rows.Count).End(xlUp).Row<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]For i = lr To 1 Step -1<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]If Range("O" & i).Value = "-" AndRange("O" & i).Offset(0, -9).Value = "-" AndRange("O" & i).Offset(0, -5).Value = "ImpactAssessment" Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insert shift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Copy<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).EntireRow.Insertshift:=xlShiftDown<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]            Range("O"& i).Value = "01DZ - BZA"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]           Range("O" & i).Offset(1).Value = "02DZ - BZA"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).Offset(2).Value = "03DZ -BZB"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]           Range("O" & i).Offset(3).Value = "04DZ - BZB"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).Offset(4).Value = "05DZ -BZC"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]            Range("O"& i).Offset(5).Value = "06DZ - BZC"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).Offset(6).Value = "07DZ -BZD"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).Offset(7).Value = "08DZ -BZD"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]           Range("O" & i).Offset(8).Value = "09DZ - BZE"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).Offset(9).Value = "10DZ -BZF"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).Offset(10).Value = "11DZ -BZG"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]           Range("O" & i).Offset(11).Value = "12DZ - BZH"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Arial][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]Range("O" & i).Offset(12).Value ="***"<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]           End If<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]            Next<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=3][COLOR=#000000]End Sub<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/CO[/COLOR][/SIZE][/FONT]DE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
runtime error 91: object variable or with block variable not set
Replies
2
Views
147
benefuexx
B
C
  • Solved
Trouble with vba 'loop' finding last row
Replies
5
Views
316
CaptainGravyBum
C
W
  • Question
VBA InputBox without entry quitting Sub/Macro
Replies
1
Views
93
Micron
M
S
  • Solved
VBA - Runtime Error 6 - Overflow
Replies
5
Views
81
ststern45
S
G
  • Solved
Add total row with a loop
Replies
4
Views
133
gberg
G

Forum statistics

Threads
1,214,643
Messages
6,120,702
Members
448,980
Latest member
CarlosWin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top