Compare Two Columns in two different workbooks using VBA

[sravanrisho]

The below one has written with specific range and in the same workbook in two sheets(sheet1,Sheet2)

looking for your help guys,If row range changes dynamically(no specific range) and in two different books.how to write
Different way of approaching the common column in excel is also accepted

 

[Yongle]

Welcome to the forum
Did you forgot to paste your code into the vba code box ?

 

[sravanrisho]

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

 

[Yongle]

2 errors I spotted in your code (same error)

A
Set rMyRng = .Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row)
Set rMyRng = .Range("A1:B" & .Range("B" & Rows.Count).End(xlUp).Row)
B
Set rCompare = .Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row)
Set rCompare = .Range("A1:B" & .Range("B" & Rows.Count).End(xlUp).Row)

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

End Sub

Do you need any other help ?

 

[sravanrisho]

Hey Thnaks yongle "Range if that is fixed,my real case problem is the row range is not fixed and column A in two different workbooks " is it psossible to help me that