0

Here is my code to cut and past a row onto the exclusions sheet if it matches a value within the Yet_another_array array, however its cutting and pasting all rows on Sheet Main, ideally only 29 rows (the 29 UIDs within the Yet_another_array array) should be cut and appended to the exclusions sheet.

     ReDim aCheck(1 To LR, 1 To LC)
                            Dim ASR As Worksheet, LS As Worksheet

                            Set ASR = ActiveWorkbook.Sheets("Main")
                            Set LS = ActiveWorkbook.Sheets("Exclusions")


            For i = 2 To LR
                    aCheck_Row = aCheck_Row + 1
                        aCheck(aCheck_Row, 1) = cells(i, cA)      'Security 'previously was fund #

                        Do
                            If IsError(Application.Match(aCheck(aCheck_Row, 1), Yet_Another_array, 1)) Then
                            'MsgBox "Found"

                            'If cells(i, 3) = Yet_Another_array(Yet_Another_array_Row, 1) Then
                            If IsError(Application.Match(cells(i, 3), Yet_Another_array, 0)) Then

                             ASR.cells(i, "C").EntireRow.Cut Destination:=LS.Range("A" & LS.Rows.Count).End(xlUp).Offset(1)
                                End If
                            End If

                            Exit Do

                        Loop While Not IsEmpty(aCheck)

            Next i
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
phillipsK
  • 1,466
  • 5
  • 29
  • 43
  • 2
    do you really want us to read through this incredibly long code (including commented out code) and try to decipher what is wrong? please edit the code to only the relevant parts. also try stepping through your code line-by-line to see where it behaves differently than as expected if you have not already. this exercise will most likely lead you to the source of the error. Also read up on [Avoiding Select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). It will help a lot. – Scott Holtzman Jul 01 '16 at 18:17
  • @ScottHoltzman I agree. It was just on my last post where I received a comment to post my full code. I edited the code down and I have tried stepping through I need a conditional to match the cell on the activesheet with the value in `(Yet_Another_array_Row, 1)` prior to cutting and pasting – phillipsK Jul 01 '16 at 18:37
  • *I have tried stepping through* - so what did you find was happening that should not be? or vice-versa? – Scott Holtzman Jul 01 '16 at 18:46
  • The code removes every row on the sheet whereas I would only like a row to be cut and pasted to the destination sheet if the row contains a value within the `Yet_Another_array` array The second `application.match` conditional is not working as I would have hoped `Do If IsError(Application.Match(aCheck(aCheck_Row, 1), Yet_Another_array, 1)) Then If IsError(Application.Match(cells(i, 3), Yet_Another_array, 0)) Then` – phillipsK Jul 01 '16 at 18:49
  • Why are you using `iserror`? – findwindow Jul 01 '16 at 19:34

0 Answers0