VBA - Copy matching rows to other worksheet

[justhumm]

I'm not terribly good with VBA, so this is probably a silly question...I took a look through the forums, but couldn't figure it out...

In worksheet-A, I have a single cell with criteria that I want to look up. Say "Match2".

In worksheet-B, there are a bunch of data rows. I want to put all of those in "arrayB".

When a data row in "arrayB" matches the criteria from worksheet-A, I want to put that row of data row into "arrayA".

When I've found all of the matching data rows, I want to paste "arrayA" into a range in worksheet-A.

Problem 1...
My code is pasting arrayA into worksheet-B...it's ignoring my "With wsA" statement at the end for some reason.

Problem 2...(forgetting about problem 1 for a moment)
While debugging, I can see that the loop is creating each row in arrayA correctly, but when it goes to paste arrayA (say a 3x3 array) into the range, it pastes blank rows and the last data row.

Does anyone know why it's not pasting all of the data in the array?...thanks!

Option Explicit     'Requires that all variables be defined
'------------------------------------------------------------
'Declare Variables
'------------------------------------------------------------
Dim wsA As Worksheet, wsB As Worksheet
Dim rItemNo As Range                    ' Cell Containing Item Number to Be Looked up
Dim FirstRow As Long, LastRow As Long, LastRowAll As Long
Dim FirstCol As Long, LastCol As Long, LastColAll As Long
Dim i As Long, j As Long, k As Long
Dim arrayA As Variant, arrayB As Variant
'------------------------------------------------------------
Sub PriceLookUp()
    '--------------------------------------------------------------------------------
    'Enter user-defined values
    '------------------------------------------------------------
    Set wsA = ThisWorkbook.Sheets("PriceLookup") 'Make sure these actually match the Sheet Names
    Set wsB = ThisWorkbook.Sheets("2009")
    Set rItemNo = wsA.Cells(7, "A")    'Cell containing Item Number to be looked up
    '------------------------------------------------------------
    LastRowAll = wsA.Rows.CountLarge
    LastColAll = wsA.Columns.CountLarge
    FirstRow = wsB.Range("B:B").Find(What:="Column1", LookIn:=xlValues, lookat:=xlWhole).Row + 1
    LastRow = wsB.Cells(wsB.Rows.Count, "B").End(xlUp).Row
    FirstCol = wsB.Cells(1, "B").Column
    LastCol = wsB.Cells(1, "D").Column
    '------------------------------------------------------------
    'Set initial dimensions of arrays
    '------------------------------------------------------------
    ReDim arrayB(1 To LastRow - FirstRow + 1, 1 To LastCol - FirstCol + 1)
    ReDim arrayA(1, 1 To UBound(arrayB, 2))
    ' At once, read all the cell values to be looked through into a local array
    With wsB
        arrayB = Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol))
    End With 
    '------------------------------------------------------------
    'Begin loop to...
    ' Compare the index column of each row in arrayB with the match value from wsA
    ' If they match, insert the matching row from arrayB into arrayA
    '------------------------------------------------------------
    i = 0
    j = 0
    k = 0
    For i = 1 To UBound(arrayB, 1)
        If rItemNo.Value = arrayB(i, 1) Then
            j = j + 1       ' counter for destination rows
            ReDim arrayA(1 To j, 1 To UBound(arrayB, 2))
            For k = 1 To UBound(arrayB, 2)
                arrayA(j, k) = arrayB(i, k)
            Next k
        End If
    Next i
    '------------------------------------------------------------  
    i = 15                          ' first row to paste data into
    j = i + UBound(arrayA, 1) - 1   ' last row to paste data into
    k = UBound(arrayA, 2) - 1
    '------------------------------------------------------------
    ' Paste matching values that were inserted into arrayA
    ' into a range in worksheetA
    '------------------------------------------------------------
    With wsA
        Range(Cells(i, 2), Cells(j, 2 + k)).Value = arrayA
    End With 
End Sub

 

[justhumm]

...bump...

 

[justhumm]

Just for purposes of noting it down, this is a different forum discussion where I found the solution...

VBA - Copy matching rows to other worksheet - Microsoft VBA - Visual Basic for Applications - Eng-Tips