Compare Two Columns in two different workbooks using VBA

Question

The code below is written using a specific range in one workbook with two sheets (Sheet1, Sheet2).

Sub CheckAvailability()
   Dim rMyRng As Range, rCompare As Range, r As Range, lFound As Long, 
   blStatus As Boolean

   Application.ScreenUpdating = False

   With Sheets("Sheet1")
      Set rMyRng = .Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row)
   End With

   With Sheets("Sheet2")
      Set rCompare = .Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row)
   End With

   For Each r In rMyRng.Rows
      With r
        .Select
         blStatus = False
         lFound = Application.CountIfs(rCompare.Columns(1), .Cells(1).Value, 
         rCompare.Columns(2), .Cells(2).Value)
         If lFound Then blStatus = True
             .Cells(2).Offset(, 1).Value = blStatus
      End With
 Next r

 Application.ScreenUpdating = True

How can I adjust this to work with two sheets with dynamically changing rows?

There is no free code writing service at this site. In order to qualify for help you should have made an own effort to get the job done. Post that effort here and indicate with which part of your code you require assistance. — Variatus, Feb 17 '20 at 06:28
Please check for the above one for specific range and same workbook — Sravan Kumar, Feb 17 '20 at 07:02
[Here](https://stackoverflow.com/a/10717999/9758194) is how you can use workbook variables, and [here](https://stackoverflow.com/a/11169920/9758194) you can see how to get last used rows dynamically =) — JvdV, Feb 17 '20 at 07:48

Compare Two Columns in two different workbooks using VBA

0 Answers0