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
Bookmarks