+ Reply to Thread
Results 1 to 3 of 3

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

Hybrid 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

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Modify macro that matches columns on two different sheets to return entire row of matc

    It is terrible idea to use sheet index nos in multi sheets as sheet name references. It gets hard to identify the sheet nos and test the code. I have changed few lines of the code.
    Try
    Attached Files Attached Files
    Last edited by AB33; 10-09-2014 at 10:17 AM.

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

    Re: Modify macro that matches columns on two different sheets to return entire row of matc

    Thank you AB33 this this most helpful!!

+ Reply to Thread

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