VBA - Copy matching rows to other worksheet 1

[justhumm]

Load Effects
The unfactored load applied to each structural member under consideration was exported by the analysis software for further processing.

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.

I can't figure out why it's not pasting all of the data in the array. If anyone could lend some insight, it would be appreciated...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

 

[SkipVought]

hi,

The use of the With...End With construct, requires a [highlight #FCE94F]DOT[/highlight] to reference the With OBJECT...

'
    With wsA
        [highlight #FCE94F].[/highlight]Range([highlight #FCE94F].[/highlight]Cells(i, 2), [highlight #FCE94F].[/highlight]Cells(j, 2 + k)).Value = arrayA
    End With

Same issue with the other array...

'
    With wsB
        arrayB = .Range(.Cells(FirstRow, FirstCol), .Cells(LastRow, LastCol))
    End With

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[justhumm]

Skip:

Thanks for the reply...it worked!

And just for noting it down...I figured out what the problem was with some of my array element values disappearing once the loop was finished. I was using Redim, but I forgot that it doesn't retain the values and I needed to used Redim Preserve.

With the little debugging I've done, so far, this code seems to be what I was shooting for...

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 'Microsoft converts all "integer" values to "long", anyway
Dim arrayA As Variant, arrayB As Variant
Dim rngSource 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

    ' index number of the first data row in worksheet A         ' index number of the last column in worksheet A

    LastRowAll = wsA.Rows.CountLarge            ' index number of the last row in any worksheet
    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
    '------------------------------------------------------------
    i = LastCol - FirstCol + 1
    j = LastRow - FirstRow + 1
    
    ReDim arrayB(1 To i, 1 To j)
    
    k = UBound(arrayB, 1)
    
    ReDim arrayA(1 To k, 1)
    
    '------------------------------------------------------------
    ' At once, read all the cell values to be looked through into a local array
	' Define cell range.
    ' Transpose into array because we will need to use a transposed array
    ' for the matching rows that are pasted into the destination sheet.
    '------------------------------------------------------------
    With wsB
        rngSource = .Range(.Cells(FirstRow, FirstCol), .Cells(LastRow, LastCol))
        arrayB = Application.Transpose(rngSource)
    End With
        
    '------------------------------------------------------------
    'Begin loop to copy matching data rows into a source array
    'For each row in arrayB...
    '------------------------------------------------------------
    i = 0
    j = 0
    k = 0
    For i = 1 To UBound(arrayB, 2)
        If rItemNo.Value = arrayB(1, i) Then
            ' counter for destination rows
            j = j + 1
            
            ' Increase array size
            ' Remember that "preserve" can only be used to resized array's 2nd dimension
            ReDim Preserve arrayA(1 To UBound(arrayB, 1), 1 To j)
            
            For k = 1 To UBound(arrayB, 1)
                arrayA(k, j) = arrayB(k, i)
            Next k
        End If
    Next i
    
    '------------------------------------------------------------
    ' Make sure destination cells are clear.
    ' Paste data array into destination worksheet.
    '------------------------------------------------------------
    With wsA
        ' First row to paste data into
        i = .Range("A:A").Find(What:="ITEM", LookIn:=xlValues, lookat:=xlWhole).Row + 1
        j = i + UBound(arrayA, 1) - 1   ' last row to paste data into
        k = UBound(arrayA, 2) - 1
        
        '.Range(.Cells(i, 1), .Cells(LastRowAll, LastColAll)).Clear
        
        .Range(.Cells(i, 1), .Cells(j, 1 + k)).Value = WorksheetFunction.Transpose(arrayA)
    End With
    
End Sub