VBA - Copy matching rows to other worksheet
VBA - Copy matching rows to other worksheet
(OP)
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!

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!

CODE --> VBA
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 




RE: VBA - Copy matching rows to other worksheet
The use of the With...End With construct, requires a DOT to reference the With OBJECT...
CODE
' With wsA .Range(.Cells(i, 2), .Cells(j, 2 + k)).Value = arrayA End WithSame issue with the other array...
CODE
' With wsB arrayB = .Range(.Cells(FirstRow, FirstCol), .Cells(LastRow, LastCol)) End WithSkip,
for a NUance!
RE: VBA - Copy matching rows to other worksheet
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...
CODE --> VBA
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