Replace cell with content from another cell

[Nadiasze]

Hi!
I have two separate files.
File 1 has text+number content in a column: A1, A2, A3, etc.
File 2 has the same content, and in addition, each one of these values has a corresponding string, in an adjacent column. For example: A1=A1 B1=abc, A2=A2, B2=cde
I want to replace the content of each cell in File 1 with the corresponding content from File 2.
In other words: I am in File 1, and would have to search for the presence of that value in File 2, and replace it with the corresponding content in File 2.
Does anyone know how to do this?
Thank you!

 

[Fluff]

@Fluff: Thank you for the feedback. Much appreciated.

Your welcome

 

[Nadiasze]

Hi!
Thank you for all your help.
Here is the link to the files. https://www.dropbox.com/sh/2pdffqj4zae98zm/AAC54ONJPong5stpB1x8NZcsa?dl=0
So this is what I need:
File 2 contains a complete list of "pegs" in column A. Each "peg" has an assigned function, stated in column B, (same workbook, File 2).
In my file that I named "Sheet1" because I was not sure if the code referred to the sheet or the workbook, I want to replace the "pegs" with the function. However, not all the pegs from File 2 are in Sheet1. So I need that for each cell from column A in Sheet1, the program identifies it in File 2, look on the next column from the content (function description), grab it, go back to Sheet 1 and replace the peg with that function.

 

[mumps]

This macro appears to be working:

Sub CompareLists()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object
    Dim srcWB As Workbook
    Set srcWB = Workbooks("File 2.xlsx")
    Dim desWB As Workbook
    Set desWB = ThisWorkbook
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In srcWB.Sheets("Sheet1").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
      If Not RngList.Exists(Rng.Value) Then
        RngList.Add Rng.Value, Nothing
      End If
    Next
    For Each Rng In desWB.Sheets("Sheet1").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
      If RngList.Exists(Rng.Value) Then
        Rng = srcWB.Sheets("Sheet1").Range("A:A").Find(Rng, LookIn:=xlValues, lookat:=xlWhole).Offset(0, 1)
      End If
    Next
    RngList.RemoveAll
    Application.ScreenUpdating = True
End Sub

 

[Nadiasze]

It worked!! Thank you!
The only problem that I encountered was that the list on File 2 was much longer than the one from Sheet 1, so when I ran it it only changed a few cells. Luckily an office mate happened to stop by (since I know nothing about this) and we just changed the range to A2:4600, and that solved the problem.
Thank you again for your time and patience.
I'll save this macros for future use.

This macro appears to be working:

Sub CompareLists()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object
    Dim srcWB As Workbook
    Set srcWB = Workbooks("File 2.xlsx")
    Dim desWB As Workbook
    Set desWB = ThisWorkbook
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In srcWB.Sheets("Sheet1").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
      If Not RngList.Exists(Rng.Value) Then
        RngList.Add Rng.Value, Nothing
      End If
    Next
    For Each Rng In desWB.Sheets("Sheet1").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
      If RngList.Exists(Rng.Value) Then
        Rng = srcWB.Sheets("Sheet1").Range("A:A").Find(Rng, LookIn:=xlValues, lookat:=xlWhole).Offset(0, 1)
      End If
    Next
    RngList.RemoveAll
    Application.ScreenUpdating = True
End Sub

 

[mumps]

You are very welcome. Out of curiosity, can you post the code with the change you made in the range?

 

[Nadiasze]

Yes, here it is. Highlighted in orange. It is probably not the most elegant solution, because one would have to change it depending on the file. There must be a way to tell the program to scan the whole length of column A in File 2 until the last cell with content.

Sub CompareLists() Application.ScreenUpdating = False Dim Rng As Range, RngList As Object Dim srcWB As Workbook Set srcWB = Workbooks("File 2.xlsx") Dim desWB As Workbook Set desWB = ThisWorkbook Set RngList = CreateObject("Scripting.Dictionary") For Each Rng In srcWB.Sheets("Sheet1").Range("A2:A4600") If Not RngList.Exists(Rng.Value) Then RngList.Add Rng.Value, Nothing End If Next For Each Rng In desWB.Sheets("Sheet1").Range("A2:A4600") If RngList.Exists(Rng.Value) Then Rng = srcWB.Sheets("Sheet1").Range("A:A").Find(Rng, LookIn:=xlValues, lookat:=xlWhole).Offset(0, 1) End If Next RngList.RemoveAll Application.ScreenUpdating = TrueEnd Sub

You are very welcome. Out of curiosity, can you post the code with the change you made in the range?

 

[mumps]

See if this does the trick:

Sub CompareLists()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object
    Dim srcWB As Workbook
    Set srcWB = Workbooks("File 2.xlsx")
    Dim desWB As Workbook
    Set desWB = ThisWorkbook
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In srcWB.Sheets("Sheet1").Range("A2:A" & srcWB.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)
      If Not RngList.Exists(Rng.Value) Then
        RngList.Add Rng.Value, Nothing
      End If
    Next
    For Each Rng In desWB.Sheets("Sheet1").Range("A2:A" & srcWB.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)
      If RngList.Exists(Rng.Value) Then
        Rng = srcWB.Sheets("Sheet1").Range("A:A").Find(Rng, LookIn:=xlValues, lookat:=xlWhole).Offset(0, 1)
      End If
    Next
    RngList.RemoveAll
    Application.ScreenUpdating = True
End Sub

 

[Nadiasze]

Yup!
It did.

See if this does the trick:

Sub CompareLists()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object
    Dim srcWB As Workbook
    Set srcWB = Workbooks("File 2.xlsx")
    Dim desWB As Workbook
    Set desWB = ThisWorkbook
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In srcWB.Sheets("Sheet1").Range("A2:A" & srcWB.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)
      If Not RngList.Exists(Rng.Value) Then
        RngList.Add Rng.Value, Nothing
      End If
    Next
    For Each Rng In desWB.Sheets("Sheet1").Range("A2:A" & srcWB.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)
      If RngList.Exists(Rng.Value) Then
        Rng = srcWB.Sheets("Sheet1").Range("A:A").Find(Rng, LookIn:=xlValues, lookat:=xlWhole).Offset(0, 1)
      End If
    Next
    RngList.RemoveAll
    Application.ScreenUpdating = True
End Sub

 

[mumps]

Terrific!!

 

[Nadiasze]

Dear mumps,
Do you think you can help me with another task?
It is a similar but a bit more complicated.