improve/fix this loop within loop

[daveyc18]

essentially, i'm trying to loop through many rows and if two lines have the same blotter, settlement date ("sd"), and they're a pair ....one being a Buy, other a Sell, one being Active, the other ebing Cancelled ....if that's the case, move those rows to another tab called "cancelled" and repeat the process until all pairs have been cut and pasted over

also, the pair will not necessarily be consecutive rows (e.g., the active one might be on row 10, the equivaneltn cancelled one might be row 30).

Sheets("A").Select

Application.ScreenUpdating = False


finalrow = Cells(Rows.Count, "A").End(xlUp).Row



For i = 2 To finalrow
    For j = 2 To finalrow

'establish blotter, etc. 

blotter = Range("p" & i)
net_amt = Abs(Range("e" & i))
buy_sold_indic = Range("l" & i)
sd = Range("x" & i)
cancel_indic = Range("o" & i)



    blotter2 = Range("p" & j)
    net_amt2 = Abs(Range("e" & j))
     buy_sold_indic2 = Range("l" & j)
    sd2 = Range("x" & j)
    cancel_indic2 = Range("o" & j)





'if blotter, net amount, etc. for the cancelled trade equals the blotter, net amount, etc. for non-cancelled trades, and the cancelled and non-cancelled trades buy/sold and cancel indicators don't equal to each other, then cut the cancelled row and the non-cancelled row to the Cancelled tab


If blotter = blotter2 And  net_amt = net_amt2 And sd = sd2 And  buy_sold_indic <> buy_sold_indic2 And cancel_indic <> cancel_indic2 Then



Range("a" & i).EntireRow.Cut

Sheets("Cancelled").Activate

totalrow = Cells(Rows.Count, "A").End(xlUp).Row + 1

Range("a" & totalrow).Select
ActiveSheet.Paste

Sheets("A").Activate

Range("a" & j).EntireRow.Cut


Sheets("Cancelled").Activate

totalrow = Cells(Rows.Count, "A").End(xlUp).Row + 1

Range("a" & totalrow).Select
ActiveSheet.Paste


Sheets("A").Activate


End If




Next j
Next i

 

[offthelip]

I have rewritten your code useing variant arrays which will be much much faster. I have not tested it:

Sub fast()
With Worksheets("A")
 finalrow = .Cells(Rows.Count, "A").End(xlUp).Row
  ' load all the data on the worksheet into a variant array
 inarr = .Range(.Cells(1, 1), .Cells(finalrow, 24))
 ' clear the worksheet apart from the hearder row
 .Range(.Cells(2, 1), .Cells(finalrow, 24)) = ""
 ' load the output arrays
 outarr = .Range(.Cells(1, 1), .Cells(finalrow, 24))
 cancl = .Range(.Cells(1, 1), .Cells(finalrow, 24))
 outi = 2
 cnci = 2
For i = 2 To finalrow
  'establish blotter, etc
If inarr(i, 16) <> "" Then
blotter = inarr(i, 16)
net_amt = Abs(inarr(i, 5))
buy_sold_indic = inarr(i, 12)
sd = inarr(i, 24)
cancel_indic = inarr(i, 15)
     For j = i To lastrow
          blotter2 = inarr(j, 16)
          net_amt2 = Abs(inarr(j, 5))
          buy_sold_indic2 = inarr(j, 12)
          sd2 = inarr(j, 24)
          cancel_indic2 = inarr(j, 15)
    'if blotter, net amount, etc. for the cancelled trade equals the blotter, net amount, etc. for non-cancelled trades, and the cancelled and non-cancelled trades buy/sold and cancel indicators don't equal to each other, then cut the cancelled row and the non-cancelled row to the Cancelled tab
          If blotter = blotter2 And net_amt = net_amt2 And sd = sd2 And buy_sold_indic <> buy_sold_indic2 And cancel_indic <> cancel_indic2 Then
              ' copy both rows to cancel output
            For k = 1 To 24
             cancl(cnci, k) = inarr(i, k)
             cancl(cnci + 1, k) = inarr(j, k)
             inarr(i, k) = ""
             inarr(j, k) = ""
            Next k
            cnci = cnci + 2
            Exit For
         End If
     Next j
 End If
 Next i
 ' Copy the remaining lines to the output array
 For i = 2 To finalrow
  If inarr(i, 16) <> "" Then
   For k = 1 To 24
    outarr(outi, k) = inarr(i, k)
   Next k
   outi = outi + 1
  End If
 Next i
 ' write the output to sheet A
  .Range(.Cells(1, 1), .Cells(finalrow, 24)) = outarr
 End With
 ' werite the output to the cancelled sheet
 With Worksheets("Cancelled")
.Range(.Cells(1, 1), .Cells(finalrow, 24)) = cancl
 
 End With
 
End Sub