Search and Replace macro

Hi

Im really struggling trying to write a macro that does the following

I recieve a weekly spreadsheet which contains any changes to customer details this can be approximately 100 entries.

I have a mastersheet which then needs to be updated with the customers updated details.

Is it possible to write a macro that will

for example copy A1-G1 from the weekly workbook then search the A1 field in the Master workbook for the customer ID. If the ID is in there replace the A1-G1 with the correspoding information.
IF it isnt in there paste it in the next availble row, and with any luck do it in for all the up to 100 entries.

I hope that makes sense because its being sending me nuts just recently

thanks for reading

Hi Delnaja ... explanation is in the comments.

Cheers, MM.

Sub UpdateFromWeeklyChangeSheet()

Dim i As Integer
Dim j As Integer
Dim MasterRowCount As Integer
Dim UpdateRowCount As Integer
Dim MasterID As String
Dim UpdateID As String
Dim booMatchFound As Boolean

'i've name the sheets "Master" and "Updates".
'for simplicity, I've assumed they're in the same workbook, but you can change that if you need to.
'from your desciption, I've assumed only records with changes are in the weekly sheet.
'i've also assumed there are no blank rows - it's solid data from row 1 to the last record.

'this runs through the update sheet row by row, looks to see if the ID in column A exists
'in the master sheet; if it does it replaces the record; if it doesn't it adds it to the end.

    Sheets("Updates").Select
    UpdateRowCount = Sheets("Updates").Range("A1").End(xlDown).Row
    MasterRowCount = Sheets("Master").Range("A1").End(xlDown).Row
    
    'cater for only 1 record in either sheet (i assume there will never be none):
    If UpdateRowCount = 65536 Then UpdateRowCount = 1
    If MasterRowCount = 65536 Then MasterRowCount = 1
    
    For i = 1 To UpdateRowCount
        UpdateID = Trim(CStr(Sheets("Updates").Range("A" & i).Text))
        For j = 1 To MasterRowCount
            MasterID = Trim(CStr(Sheets("Master").Range("A" & j).Text))
            'if a match is found, update it and get the next update record:
            If MasterID = UpdateID Then
                booMatchFound = True
                Sheets("Updates").Range("A" & i).EntireRow.Copy Destination:=Sheets("Master").Range("A" & j)
                GoTo GetNextUpdateRow
            End If
            'if at the end of the master list and no match found, add to the end,
            'increment the master row count, and get the next update row:
            If j = MasterRowCount And booMatchFound = False Then
                Sheets("Updates").Range("A" & i).EntireRow.Copy Destination:=Sheets("Master").Range("A" & j)
                MasterRowCount = MasterRowCount + 1
                GoTo GetNextUpdateRow
            End If
        Next j
GetNextUpdateRow:
        booMatchFound = False
    Next i
        
End Sub

I have also found this very useful. If on "Updates" sheet I only have one item to update in cells A2 to D2, how do I change the code?

Thanks

Hi ... I'm not sure I'm following what you mean; do you mean that only 1 cell in the range A2-D2 in the updates sheet is different and therefore only these need updating in the master? I don't really understand I'm afraid, and I doubt this is what you mean since it makes no difference ... Perhaps if you post a small sample of data from your update sheet and explain why posting the whole row won't work for your data I can help ... The only reason I can think of is if not all the columns in the update are in the master? If that's the case, then the bit in the original code that does the pasting is:

Sheets("Updates").Range("A" & i).EntireRow.Copy Destination:=Sheets("Master").Range("A" & j)

And assuming you want to only replace cells in columns A to D then you'd replace this with something like:

Sheets("Updates").Range("A" & i & ":D" & i).Copy Destination:=Sheets("Master").Range("A" & j & ":D" & j)

If I've missed the point, please try to explain & I'll see what I can do.

Cheers, MM.

Yes, I mean, I am only updating one row at a time. So only those cells A3 to D3 on the "Updates" sheet will be used to update the "Master" list, Thanks