OzGrid

How to compare 2 columns in different worksheet and overwrite existing record if a match found

Requirement:

The user has two worksheets in a workbook.

Master Sheet

Update Sheet

The user wants to update the record in Master sheet from the Update sheet, so after the update the records in Master sheet will look like this

Master Sheet

Example, a comparison will be done based on column C in both Master and Update sheet, if a match is found, it will overwrite the entire row in Master sheet. The user had written a portion of the code, and the user seems to be stuck in how to overwrite the entire row.

Sub fruitUpdate()  

Dim rangeUpdt, rangeMstr As Range  
Dim ws1, ws2 As Worksheet

Set ws1 = Worksheets("Master")
Set ws2 = Worksheets("Fruit Update")  

On Error Resume Next
With ws1    
Set rangeMstr = .Range("C4", .Range("C" & Rows.Count).End(xlUp)) 'records start at C4
End With

With ws2    
Set rangeUpdt = .Range("C10", .Range("C" & Rows.Count).End(xlUp)) 'records start at C10
End With    

If rangeMstr = rangeUpdt Then     'overwrite, copy to masterlist (here's the part that I stuck)    
End If

End Sub

Solution:

Sub FruitUpdate2()

Dim rangeUpdt, rangeMstr As Range
Dim ws1, ws2 As Worksheet
Dim c As Range
Dim res As Variant

Set ws1 = Worksheets("Master")
Set ws2 = Worksheets("Fruit Update")
With ws1
  Set rangeMstr = .Range("C4", .Range("C" & Rows.Count).End(xlUp)) 'records start at C4
End With
With ws2
  Set rangeUpdt = .Range("C10", .Range("C" & Rows.Count).End(xlUp)) 'records start at C10
End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Ideally Both Ranges should start in Row 2 ....'''''''''''''''''''''''''''''''''''''''''
''' otherwise you have to adjust for the Offset ... in this case + 9 ''''''''''''''''''''''
    For Each c In rangeMstr
      res = Application.Match(c, rangeUpdt, 0)
      If Not IsError(res) Then
        c.Offset(0, 1) = ws2.Range("D" & res + 9)
        c.Offset(0, 2) = ws2.Range("E" & res + 9)
      End If
    Next c
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Carim.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery