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

 

[Fluff]

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

 

[CharlieRog]

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")
MyResolutionCell.Select

Selection.Value = MyResolutionType

Sheets("Sheet 2").Select

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

If MyClearCodeCell.value = MyResolutionType


MsgBox MyResolutionType

 

[Fluff]

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/

 

[freerskys]

Great work Fluff, this code is lightning fast. Is there a way to highlight in red, the matched cells in Sheet2 of column A?

 

[Fluff]

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

 

[freerskys]

Great!, works fast, you make it look so easy.
Thanks.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[stevep2k21uk]

hi,
i was reading this post and its something i need help with myself, i have an inventory scanner that scans a part then update quantity, it saves as an comma separated txt file called InvMgr.txt.
im wanting to :

1. run the macro.
2. it imports a the file mentioned above into sheet2 from a specified location.
3. then compares "sheet2 column A" with "sheet1 column B".
4. then copy the cell adjacent (sheet2 column B)
5. then paste to the cell on sheet1 column M. see below:

SHEET1

A

B

C

D

E

F

G

H

I

J

K

L

M

1

000005 - COMPARE WITH SHEET2 COL A

6 PASTE FROM SHEET 2 COL B

2

SHEET2​
A​	B​
000005	6 - COPY THIS CELL

does this make sense?

 

[Fluff]

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