Want to compare sheet1 column C list to sheet2 column H list and if match copy column H row A to Z to Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2).

Errors out on the varOut = MyArr2(ii).Offset(, -7).Resize(1, 26).

I am aware that MyArr2 only sees the range on sheet2 as cells H1:H2, while MyArr1 shows 10 elements...??? So for sure the code is going to fail on that alone, but this is my shot at it.

Here is what the compare columns hold on sheet1 & sheet2. So the only sheet2 rows A to Z that would NOT get copied to sheet3 are 33 and 44. No match.

Code is in standard module.
Trying to use arrays to avoid the slow "For each c in First Range" & "For each cc in Second Range", but almost looks like that is what I am doing with the arrays.

Howard


1 8
2 7
3 6
4 5
11 33
5 4
6 3
7 2
8 1
22 44


Sub ColumnsCompare()

Dim i As Long, ii As Long

Dim MyArr1 As Variant
Dim MyArr2 As Variant

Dim varOut As Variant

MyArr1 = Sheets("Sheet1").Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row).Value
MyArr2 = Sheets("Sheet2").Range("H2:H" & Cells(Rows.Count, "H").End(xlUp).Row).Value

Application.ScreenUpdating = False


For i = LBound(MyArr1) To UBound(MyArr1)

For ii = LBound(MyArr2) To UBound(MyArr2)

If MyArr1(i, 1) = MyArr2(ii, 1) Then

'/ set the data to copy
varOut = MyArr2(ii).Offset(, -7).Resize(1, 26)

Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2) = varOut

End If

Next 'ii

Next 'i

Application.ScreenUpdating = True
End Sub