VBA To find match of cell value and copy adjacent cell when match found


New Member
Looking for vba code to see if data from 2 different cells on 2 different sheets if it matches then it copies the cell to the right on sheet 2 and pastes it to the cell on the right on sheet 1.

All the data in sheet 1 column M is present on sheet 2 column A. So when it finds a match in column M (sheet 1) with the same value in column A (sheet 2) it copies value of adjacent cell in column B (sheet 2) and pastes it on sheet 1 in column N. And then moves onto the next until complete


MrExcel MVP, Moderator
How about
Sub CharlieRog()
    Dim Cl As Range
    Dim Dic As Object
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Sheet2")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            Dic(Cl.Value) = Cl.Offset(, 1).Value
        Next Cl
    End With
    With Sheets("Sheet1")
        For Each Cl In .Range("M2", .Range("M" & Rows.Count).End(xlUp))
            If Dic.exists(Cl.Value) Then Cl.Offset(, 1).Value = Dic(Cl.Value)
        Next Cl
    End With
End Sub


New Member
IT WORKS!!! Thanks so much! Could you explain to me what each bit is doing just so I can try learn a few things?

I was trying to make one and came up with...

Sheets("Sheet 1").Select

For Each MyResolutionCell In Range("M2:M8399")

Selection.Value = MyResolutionType

Sheets("Sheet 2").Select

For Each MyClearCodeCell In Range("A2:A319")

If MyClearCodeCell.value = MyResolutionType

MsgBox MyResolutionType


MrExcel MVP, Moderator
The first part loops through the cells in sheet2 & adds the col A values to a dictionary with the column B values as the item.
The 2nd part loops through the cells in sheet1 & if the cell value exists in the dictionary, it adds the item from the dictionary to column N.
For more on dictionaries have a look here https://excelmacromastery.com/vba-dictionary/

Some videos you may like

This Week's Hot Topics