to stay with your code
Sub MatchColumnsCondition()
Dim sht1 As Worksheet, sht2 As Worksheet, sht3 As Worksheet
Dim lr1 As Long, lr2 As Long
Dim chk1 As Variant, chk2 As Variant
Dim i As Long, j As Long
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
chk1 = sht1.Range("A1:B" & lr1).Value
chk2 = sht2.Range("A1:A" & lr2).Value
For i = LBound(chk1) To UBound(chk1)
For j = LBound(chk2) To UBound(chk2)
If chk1(i, 1) = chk2(j, 1) And chk1(i, 2) = "a" Then
sht3.Cells(sht3.Rows.Count, "A").End(xlUp).Offset(1).Value = chk1(i, 1)
End If
Next
Next
End Sub
where in your original code:
1) Dim sht1, sht2, sht3 As Worksheet
would actually result in:
Dim sht3 As Worksheet, sht1 As Variant, sht2 As Variant
since unexplicitly declared variables (Dim sht1, sht2, ...) would be implicitly assumed as of Variant type
hence explicity declare all Worksheet type variables, like Dim sht1 As Worksheet, sht2 As Worksheet, sht3 As Worksheet
2) LBound(chk1) To UBound(chk1) and LBound(chk2) To UBound(chk2)
LBound() and UBound() functions accept an array as parameter.
In order to have an array out of a Range you have to take its Value property
then
- ok with
Dim chk1, chk2 As Variant
that would result as Dim chk1 As Variant, chk2 As Variant and which is fine since a Variant is the right type we need to store a range values into
Set chk1 = sht1.Range("A1:A" & lr1) turns into chk1 = sht1.Range("A1:B" & lr1).Value, since you don't Set an array and you need it to store column B values, too
Set chk2 = sht2.Range("A1:A" & lr1) turns into chk2 = sht2.Range("A1:A" & lr1).Value
you don't need out3 , hence don't declare neither Set it
3) Offset() is a Range class property, while arrays have no methods nor properties
to get some value in 2nd column of a 2D array you use the column index like chk1(i, 2)
finally) sht3.Range("A" & lr3) would keep writing in the same cell over and over again
hence either you update lr3 (with some lr3 = lr3 + 1 before End If) or you need a dynamic range reference always pointing to sht3 column A first empty cell after last not empty one, like sht3.Cells(sht3.Rows.Count, "A").End(xlUp).Offset(1)
EDIT: added a different approach
provided (as per shown data) column B of Sheet1 have either "a"s or blank cells, then you could avoid loops and use AutoFilter() and Specialcells() methods of Range object, as follows (explanations in comments):
Sub MatchColumnsCondition2()
Dim sht1 As Worksheet, sht2 As Worksheet, sht3 As Worksheet
Dim chk2 As Variant
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
chk2 = sht2.Range("A1", sht2.Cells(sht2.Rows.Count, "A").End(xlUp)).Value
With sht1 ' reference "sheet1"
With .Range("B1:A" & .Cells(sht1.Rows.Count, "A").End(xlUp).Row) 'reference referenced sheet columns A:B range from row 1 down to column A last not empty cell row
.Rows(1).EntireRow.Insert ' insert a "helper" row for headers
With .Offset(-1).Resize(.Rows.Count + 1) ' reference referenced range plus added header row
.Rows(1).Value = Array("h1", "h2") ' write dummy headers
.AutoFilter field:=1, Criteria1:=Application.Transpose(chk2), Operator:=xlFilterValues ' filter referened range on its first column with sheet2 column A values
.Resize(.Rows.Count - 1, 1).Offset(1, 1).SpecialCells(xlCellTypeVisible).SpecialCells(XlCellType.xlCellTypeConstants).Offset(, -1).Copy Destination:=sht3.Range("A1") ' copy referenced range second column filtered cells (skipping headers) with some constant value and paste to sheet 3 from cell A1
.Rows(1).Delete xlUp ' delete "helper" row
End With
End With
End With
End Sub
