Forum Discussion

MicrosoftNewbie121's avatar
MicrosoftNewbie121
Copper 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 column value corresponding from Sheet 2 to Sheet 1. (and loop this until the last value in Sheet 2)

 

could you please help me ? (i would like to achive this using macro VBA)

 

Here is an example of the excel

excel
Macros and VBA

2 Replies

Sort By
  • 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.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 03, 2024

    MicrosoftNewbie121 

    For example:

    Sub FillAddress()
    Dim m1 As Long
    Dim m2 As Long
    With Worksheets("Sheet2")
        m2 = .Range("A" & .Rows.Count).End(xlUp).Row
    End With
    With Worksheets("Sheet1")
        m1 = .Range("A" & .Rows.Count).End(xlUp).Row
        With .Range("C2:C" & m1)
            .Formula2 = "=IFERROR(INDEX(Sheet2!$C$2:$C$" & m2 & _
                ", MATCH(1, (Sheet2!$A$2:$A$" & m2 & _
                "=A2)*(Sheet2!$B$2:$B$" & m2 & "=B2), 0)), """")"
            .Value = .Value
        End With
    End With
End Sub

Resources