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.