INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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!



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

hi,

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 With 

Same issue with the other array...

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA - Copy matching rows to other worksheet

(OP)
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...

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 



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close