Search and Replace Rows

[Guest]

I have two tables of data, one small with about ten rows of data and the
other thousdands of rows long. I want to lookup the records in the smaller
table in the larger table and where there is a match, replace the record in
the larger table with the one from the smaller table. I would need to select
a record from the small table, go over to the sheet with the larger table and
do a lookup of that record, paste when there is a match and return to the
small table for the next record.

Any pointers? Thanks.

 

[Nigel]

Hi, try this code, it scans sheet1 finds the first match on sheet 2 and
copies columns B
& C to sheet 2
change references to suit your needs.

'-----------------------------------------------------------------
Sub Update()
Application.ScreenUpdating = False

Dim wsNew As Worksheet, wsOld As Worksheet
Dim xLastRowNew As Long, xLastRowOld As Long
Dim xRN As Long, xRO As Long, XUpdates As Long

Set wsNew = Sheets(1)
Set wsOld = Sheets(2)

' use column 1 ("A") to get last row of data on each sheet
xLastRowNew = wsNew.Cells(Rows.Count, 1).End(xlUp).Row
xLastRowOld = wsOld.Cells(Rows.Count, 1).End(xlUp).Row

' scan new data - assumes first data row is row 1
XUpdates = 0
With wsNew
For xRN = 1 To xLastRowNew
For xRO = 1 To xLastRowOld
' check for match
If .Cells(xRN, 1) = wsOld.Cells(xRO, 1) Then
' copy values chosen to copy items in columns 2 and 3
wsOld.Cells(xRO, 2) = .Cells(xRN, 2)
wsOld.Cells(xRO, 3) = .Cells(xRN, 3)
XUpdates = XUpdates + 1
Exit For
End If
Next xRO
Next xRN
End With
Application.ScreenUpdating = True

MsgBox "Completed - Updated: " & CStr(XUpdates)

End Sub