Forum Discussion

MicrosoftNewbie121's avatar
MicrosoftNewbie121
Brass Contributor
Apr 03, 2024

VBA Excel - Verify and Paste value if 2 Cells Match

Hello, i would like to verify the match of 2 cell values like "First Name" & "Last Name" from Sheet 2 and verify if it match any value in Sheet 1 if it does match the 2 cells info then copy past 3rd ...
excel
Macros and VBA
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Apr 03, 2024

MicrosoftNewbie121 

Sub Address()

    Dim objDic As Object
    Dim lastnamefirstname As Range
    Dim i As Long
    Dim scriptingkey As String
    Dim resultData
    
    Range("C2:D" & Cells(Rows.Count, "B").End(xlUp).Row).Clear
    
    Set objDic = CreateObject("scripting.dictionary")
    Set lastnamefirstname = Sheets("Sheet2").Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row)
    
    resultData = lastnamefirstname.Value
    
    For i = LBound(resultData) To UBound(resultData)
        scriptingkey = resultData(i, 1) & "|" & resultData(i, 2)
        objDic(scriptingkey) = resultData(i, 3)
    Next i
    
    Set lastnamefirstname = Range("A2:D" & Cells(Rows.Count, "B").End(xlUp).Row)
    resultData = lastnamefirstname.Value
    
    For i = LBound(resultData) To UBound(resultData)
        scriptingkey = resultData(i, 1) & "|" & resultData(i, 2)
        If objDic.Exists(scriptingkey) Then
            resultData(i, 3) = objDic(scriptingkey)
        Else
            resultData(i, 4) = "does not exist in sheet2"
        End If
    Next i
    
    lastnamefirstname.Value = resultData

End Sub

Alternatively you can click the button in cell H2 in sheet1 of the attached file to run this macro.

Resources