# (Solved) – vba excel compare 2 columns with condition for 3rd column

In sheet1 I have two columns:

In Sheet2 I have one column:

In Sheet3 I want to get the following result:

Arguments to get the data in Sheet3 are: Value of column A of Sheet2 equals a value in column A of Sheet1 (can be random row #) AND if it equals then value in column B of Sheet1 should be “a”.

I wrote the following:

``````Sub MatchColumnsCondition()

Dim sht1, sht2, sht3 As Worksheet
Dim lr1, lr2, lr3 As Long
Dim chk1, chk2 As Variant
Dim out3 As Range
Dim dup As Boolean
Dim i, j

Set sht1 = ThisWorkbook.Worksheets("Sheet1") 'data to search in including condition
Set sht2 = ThisWorkbook.Worksheets("Sheet2") 'data to search from
Set sht3 = ThisWorkbook.Worksheets("Sheet3") 'output data

lr1 = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row
lr2 = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row
lr3 = sht3.Cells(sht3.Rows.Count, "A").End(xlUp).Row

Set chk1 = sht1.Range("A1:A" & lr1)
Set chk2 = sht2.Range("A1:A" & lr2)
Set out3 = sht3.Range("A1:A" & lr3)

For i = LBound(chk1) To UBound(chk1)
For j = LBound(chk2) To UBound(chk2)
If chk1(i, 1) = chk2(j, 1) And chk1.Offset(, 1) = "a" Then
sht3.Range("A" & lr3) = chk1(i, 1)
End If
Next j
Next i

End Sub
``````

but I keep running into errors with it, but I don’t know how to make it work properly.