My Never ending ARRAY code problems
Hi Howard,
Am Wed, 11 Feb 2015 17:00:22 -0800 (PST) schrieb L. Howard:
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.
better use a range for outputthan an array.
Try:
Sub ColumnsCompare()
Dim i As Long, ii As Long, n As Long
Dim MyArr1 As Variant
Dim MyArr2 As Variant
Dim rngBig As Range
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
If rngBig Is Nothing Then
Set rngBig = Sheets("Sheet2").Range _
(Cells(ii + 1, 1), Cells(ii + 1, 26))
Else
Set rngBig = Union(rngBig, Sheets("Sheet2") _
.Range(Cells(ii + 1, 1), Cells(ii + 1, 26)))
End If
End If
Next 'ii
Next 'i
Sheets("Sheet3").Range("A1").Resize(rngBig.Rows.Co unt, _
rngBig.Columns.Count).Value = rngBig.Value
Application.ScreenUpdating = True
End Sub
Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
|