ExcelVBA匹配多列并獲取值

我要做的是匹配 2 個不同表中的值并將值復制到目標表中。我知道這需要多個回圈/條件，我正在努力解決。

目標是使用輔助表 (SA) 中的匹配將匹配值從源表 (SE) 復制到目標表 (FB) 中的每一行。

這張圖片顯示了我想要實作的目標： Tables.jpg

請注意，表“SA”的“C”列中沒有唯一鍵值。

到目前為止，我的代碼如下：

Sub MatchTables()


    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim i As Long, j As Long
    Dim newSheetPos As Integer

    Set ws1 = ActiveWorkbook.Sheets("FB") 'Range: last row
    Set ws2 = ActiveWorkbook.Sheets("SA") 'Range: rows 5 to 84
    Set ws3 = ActiveWorkbook.Sheets("SE") 'Range: last row

    For i = 2 To ws1.Cells(ws1.Rows.Count, 3).End(xlUp).Row
        For j = 5 To 84

            If ws1.Cells(i, 3).Value = ws2.Cells(j, 3).Value Then

                If ws2.Cells(i, 3).Value = ws3.Cells(j, 5).Value Then
                    ws3.Cells(j, 6).Copy ws1.Cells(i , 16)
                Else
                End If
            Else
            End If

        Next j
    Next i
End Sub

非常感謝您的幫助。

uj5u.com熱心網友回復：

（超級）雙重查找

• 為簡化起見，假設每個查找列包含至少 2 行資料且沒有錯誤值或空白。

Sub SuperLookup()

    Const sName As String = "SE"
    Const sfRow As Long = 2
    Const slCol As String = "E" ' 4.) ... here and return...
    Const svCol As String = "F" ' 5.) ... this...
    
    Const lName As String = "SA"
    Const lRowsAddress As String = "5:84"
    Const llCol As String = "C" ' 2.) ... here and return...
    Const lvCol As String = "Q" ' 3.) ... this to look it up...
    
    Const dName As String = "FB"
    Const dfRow As Long = 2
    Const dlCol As String = "C" ' 1.) Look up this...
    Const dvCol As String = "P" ' 6.) ... here.
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Worksheets(sName)
    Dim slRow As Long: slRow = sws.Cells(sws.Rows.Count, slCol).End(xlUp).Row
    Dim srg As Range
    Set srg = sws.Cells(sfRow, slCol).Resize(slRow - sfRow   1)
    Dim sData As Variant: sData = srg.EntireRow.Columns(svCol).Value
    
    Dim lws As Worksheet: Set lws = wb.Worksheets(lName)
    Dim lrg As Range: Set lrg = lws.Rows(lRowsAddress).Columns(llCol)
    Dim lData As Variant: lData = lrg.EntireRow.Columns(lvCol).Value
    
    Dim dws As Worksheet: Set dws = wb.Worksheets(dName)
    Dim dlRow As Long: dlRow = dws.Cells(dws.Rows.Count, dlCol).End(xlUp).Row
    Dim drCount As Long: drCount = dlRow - dfRow   1
    Dim drg As Range: Set drg = dws.Cells(dfRow, dlCol).Resize(drCount)
    Dim dlData As Variant: dlData = drg.Value
    Set drg = drg.EntireRow.Columns(dvCol)
    Dim dvData As Variant: ReDim dvData(1 To drg.Rows.Count, 1 To 1)
    
    Dim sIndex As Variant
    Dim lIndex As Variant
    Dim lValue As Variant
    Dim dValue As Variant
    Dim dr As Long
    
    For dr = 1 To drCount
        dValue = dlData(dr, 1)
        lIndex = Application.Match(dValue, lrg, 0)
        If IsNumeric(lIndex) Then
            lValue = lData(lIndex, 1)
            sIndex = Application.Match(lValue, srg, 0)
            If IsNumeric(sIndex) Then
                dvData(dr, 1) = sData(sIndex, 1)
            'Else ' not found in source; do nothing
            End If
        'Else ' not found in lookup; do nothing
        End If
    Next dr

    drg.Value = dvData
    
    MsgBox "Super lookup has finished.", vbInformation

End Sub

標籤：擅长 vba 循环 抬头 匹配

上一篇：物品清單和物品清單的區別

下一篇：如何除錯Kubernetes中的未知欄位錯誤？