Replace range if match found

[Harshil Mehta]

The code finds a match for the each cells in Sheet2_Column_1 from Sheet1_Column_1....If a match is found then replace the range in Sheet2_Columns_1 to 5 with Sheet1_Columns_7 to 11.

The blow code only replaces the cell data in Sheet2_Column_1 with Sheet1_Column_7 when the match is found.

Is there any way I could replace all the other column data at once?

Sub Replace_range()
    Dim Cl As Range
    Dim Dic As Object
    
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Sheet1")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            Dic(Cl.Value) = Cl.Offset(, 6)
        Next Cl
    End With
    With Sheets("Sheet2")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            If Dic.exists(Cl.Value) Then
               Cl.Value = Dic(Cl.Value)
               
            End If
        Next Cl
    End With
End Sub

 

[Fluff]

How about

Sub Replace_range()
    Dim Cl As Range
    Dim Dic As Object
    
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Sheet1")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            Dic(Cl.Value) = Cl.Offset(, 6).Resize(, 5)
        Next Cl
    End With
    With Sheets("Sheet2")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            If Dic.Exists(Cl.Value) Then
               Cl.Resize(, 5).Value = Dic(Cl.Value)
               
            End If
        Next Cl
    End With
End Sub

 

[Harshil Mehta]

Worked perfectly fine. Thank you so much for the quick response.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Harshil Mehta]

You're welcome & thanks for the feedback.

Hey Fluff, is it possible to edit the code to delete the entire row if match is found?

The below code does not delete the row in one go.

Sub Delete_Newlyadded()
    
   Dim Cl As Range
    Dim Dic As Object
    
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Definition.Temp")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            Dic(Cl.Value) = Cl.Value
        Next Cl
    End With
    With Sheets("New.Temp")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            If Dic.Exists(Cl.Value) Then
               Cl.EntireRow.Delete
               
            End If
        Next Cl
    End With
            
End Sub

 

[Fluff]

As this is a totally different question, it needs a new thread. Thanks