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

[CharlieRog]

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

 

[stevep2k21uk]

Please start a thread of your own for this question. Thanks

hi

i have but because you helped with this question thought id add it here too,,

 

[MarcusG]

In future you would be better off starting a new thread, rather than posting to an old one.
I only saw your post by chance.

How about

Sub freerskys()
    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) = Array(Cl, 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)(1)
               Dic(Cl.Value)(0).Interior.Color = vbRed
            End If
        Next Cl
    End With
End Sub

Can this be modified to match a wildcard value on sheet1? Example: if Cl value on sheet 2 = 300 and Cl value on sheet 1 = D300

 

[Fluff]

Please start a thread of your own, for this question. Thanks

 

[CB76]

Hi,
How do you reference another workbook in the code above instead of "Sheet1" to copy the data to?
Thanks.

 

[Fluff]

Hi & welcome to MrExcel.
Please star a new thread for your question as it is different from the Ops. Thanks