VBA - Vergleichen Sie zwei Spalten in einem Arbeitsblatt mit zwei Spalten in einem anderen Arbeitsblatt

VBA-Ansatz

Unten ist ein sehr einfacher Ansatz zum Abrufen der Werte. Ich bin mir ziemlich sicher, dass es viele andere effizientere Ansätze gibt, aber ich würde mich nur damit beschäftigen, wenn sich das als zu langsam herausstellt.

Zur Verwendung - konfigurieren Sie die ersten Einstellungen gemäß den Kommentaren und hoffentlich können Sie loslegen!

Sub RetrieveValues()

    Set ws1 = Sheets("Sheet1")      'Sheet we are going to copy results into
    Set ws2 = Sheets("Sheet2")      'Sheet we are going to copy results from

    'Identify the columns to use in the sheet we are copying into
    ws1UniqueIDCol = "B"         'Is the column with the first part of our lookup key - the unique id
    ws1LineIdCol = "C"           'Is the column with the second part of our lookup key - the line id
    ws1ValToWriteCol = "D"       'Is the column we want to put the found value into
    
    ws1StartRow = 3              'The row we want to start processing first, we can calc the last row automatically
    ws1EndRow = ws1.UsedRange.Rows(ws1.UsedRange.Rows.Count).Row
    
    
    'Identify the columns to use in the sheet we are copying from
    ws2UniqueIDCol = "B"         'Is the column with the first part of our lookup key - the unique id
    ws2LineIdCol = "C"           'Is the column with the second part of our lookup key - the line id
    ws2ValToCopyCol = "D"        'Is the column we want to copy the value from
    
    ws2StartRow = 3              'The row we want to start search first, we can calc the last row automatically
    ws2EndRow = ws2.UsedRange.Rows(ws2.UsedRange.Rows.Count).Row
    
    'iterate through search terms
    For i = ws1StartRow To ws1EndRow        '
        searchKey = ws1.Range(ws1UniqueIDCol & i) & ws1.Range(ws1LineIdCol & i)
        
         'if we have a non blank search term then iterate through possible matches
        If (searchKey <> "") Then
            For j = ws2StartRow To ws2EndRow
                 foundKey = ws2.Range(ws2UniqueIDCol & j) & ws2.Range(ws2LineIdCol & j)
                  
                  'if we have a match then copy the result and end this search (so we will take the first match found only)
                 If (searchKey = foundKey) Then
                    ws1.Range(ws1ValToWriteCol & i).Value2 = ws2.Range(ws2ValToCopyCol & j).Value2
                    Exit For
                 End If
            Next
        End If
    Next

End Sub