Trying to search through list in workbook x find values in workbooks y and z and then delete row.

[HEzim]

Hi, So this was supposed to be fairly straight forward.
I have two lists in two sheets in one excel file. (Sheet 1 and Sheet 2 in Workbook A)
Then I have two other excel files with 1 sheet in each of them. Sheet 1 Work Book B. Sheet 1 Work Book C.

So Sheet 1 in Workbook "Main" has a list of values in column A. I need a vba script that can look through column A. and find the matching value in Column A of Sheet 1 workbook "Rad" and IF Found then delete the row in Sheet 1 WorkBook "Rad".

Then I need it to look Column A in Sheet 2 of work Book "Main" and find the matching value in column A Sheet 1 Work Book "Vogue". And if it finds the value then delete the row in Workbook "Vogue".

Here is the code so far.

Dim U1 As Worksheet, U2 As Workbook, U3 As Worksheet, i As Long, fn As Range, L1 As Worksheet, L2 As Workbook, L3 As Worksheet, l As Long, ln As Range

Set U1 = Workbooks("Main.xlsm").Sheets("Sheet 1")
Set U2 = Workbooks.Open("M:\Thisplace\Rad.csv")
Set U3 = U2.Sheets(1)


    For i = U3.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        Set fn = U1.Columns("A").Find(U3.Cells(i, 1).Value)
        If Not fn Is Nothing Then Rows(i).Delete
  

  U2.Close Savechanges:=True

Next

  Set L1 = Workbooks("Main.xlsm").Sheets("Sheet 2")
  Set L2 = Workbooks.Open("M:\Thisplace\Vogue.csv")
  Set L3 = L2.Sheets(1)

For l = L3.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        Set ln = L1.Columns("A").Find(L3.Cells(i, 1).Value)
        If Not ln Is Nothing Then Rows(i).Delete
  

  L2.Close Savechanges:=True

  Next

ThisWorkbook.Close Savechanges:=True


End Sub

I don't know why but I get "Cells' of Object'_Worksheet' failed - error message. Can someone please tell me what I am doing wrong?

 

[Fluff]

Try closing the workbooks after the loop not during it.

 

[HEzim]

Try closing the workbooks after the loop not during it.

Hi that seemed to work but for whatever reason it doesn't seem to complete the loop. In other words, it doesn't look through the entire list. It only finds a few of the listed items

Do you see anything wrong with the way I have written the script for the loop itself?

Thanks

 

[Fluff]

Try

If Not fn Is Nothing Then U3.Rows(i).Delete

 

[HEzim]

Try

If Not fn Is Nothing Then U3.Rows(i).Delete

Thank you.

The issue was that
1., like you said, I should have closed outside the loop.
2. I should have written it like this:

For l = L3.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
Set ln = L1.Columns("A").Find(L3.Cells(l, 1).Value)
If Not ln Is Nothing Then Rows(l).Delete

i was using i for both files as opposed to l.

 

[Fluff]

Glad you sorted it & thanks for the feedback.