Results 1 to 3 of 3

Modify macro that matches columns on two different sheets to return entire row of match

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    Post Modify macro that matches columns on two different sheets to return entire row of match

    I have a "matching" macro (below) that works well with large data sets when it finds a match it will return the a partial row, from the matched cell to the last cell in that row.

    I need it to return the entire row of the matched cell, I have tried to figure this out but have had no success.

    I have attached a simple workbook.

    Thank you for you consideration.



    Sub Match1()
    
        Dim objDic As Object: Set objDic = CreateObject("Scripting.Dictionary")
        
        Dim wsS As Excel.Worksheet
        Dim wsT As Excel.Worksheet
        Dim wsO As Excel.Worksheet
        
        Dim varSource As Variant
        Dim varDestn As Variant
        Dim v As Variant
        
        Dim lngLastRow As Long
        Dim lngLastRowT As Long
        Dim lngLastCol As Long
        Dim i As Long
        Dim j As Long
        
        
        
        Set wsS = ThisWorkbook.Sheets(3)
        Set wsT = ThisWorkbook.Sheets(2)
        Set wsO = ThisWorkbook.Sheets(4)
        
        lngLastRow = wsS.Range("A" & Rows.Count).End(xlUp).Row
        lngLastRowT = wsT.Range("A" & Rows.Count).End(xlUp).Row
        lngLastCol = wsS.Cells.Find("*", , , , 2, 2).Column
        
        varSource = wsS.Range("D2").Resize(lngLastRow - 1, lngLastCol).Value
         varDestn = wsT.Range("B2:B" & lngLastRowT).Value
        
        ReDim v(1 To lngLastRowT - 1, 1 To lngLastCol)
        
        With objDic
            .comparemode = vbTextCompare
            '\\ Build source array
            For i = LBound(varSource) To UBound(varSource)
                If Not .Exists(varSource(i, 1)) Then .Item(varSource(i, 1)) = i
            Next i
            
            '\\ Build Destination array
            For i = LBound(varDestn) To UBound(varDestn)
                If .Exists(varDestn(i, 1)) Then
                    For j = 1 To lngLastCol
                        v(i, j) = varSource(.Item(varDestn(i, 1)), j)
                    Next j
                Else
                    v(i, 1) = "#N/A"
                End If
            Next i
        End With
        
        '\\ Post back the results
        wsO.Range("A1").Value = "Filter"
        wsO.Range("A2").Resize(lngLastRowT - 1, lngLastCol).Value = v
        
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Tweak Macro to return entire row if match found
    By capson in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-16-2014, 02:32 PM
  2. Replies: 9
    Last Post: 12-16-2013, 04:02 AM
  3. [SOLVED] Macro to locate matches between data in two columns on two seperate sheets
    By bojangles_73 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-07-2012, 08:10 PM
  4. Match 2 columns in 2 sheets and return the 3rd column
    By swadson in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-16-2011, 04:36 PM
  5. Match 1 criteria if matches then copy the entire row plus 1 row above and below
    By khalid79m in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-27-2007, 11:32 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1