error object variable block when match between two files and replace

Maklil

Board Regular
Joined
Jun 23, 2022
Messages
139
Office Version
  1. 2019
Platform
  1. Windows
Hello
I no know if this code fits my project. I got this code from the internet . I want matching column B between two files . the file1 should match with file 2. if there is different the data in file 2 then should replace new data in file 1 the range is from A:E and the headers in row1
I got error object variable block in this line
VBA Code:
 m = Application.Match(rw.Cells(1).Value, wsDest.Columns("B"), 0)
VBA Code:
Sub Button2_Click()
    
    Dim OpenFileName As String
    Dim wb As Workbook
    Dim wsCopy As Worksheet
    Dim wsDest As Worksheet
    Dim m, rw As Range
    
    OpenFileName = Application.GetOpenFilename 'Select and Open workbook
    If OpenFileName = "False" Then Exit Sub
    
    Set wb = Workbooks.Open(OpenFileName, ReadOnly:=True)
    Set wsCopy = wb.Worksheets("Data") 'for example
    
    For Each rw In wsCopy.Range("A2:E" & wsCopy.Cells(Rows.Count, "A").End(xlUp).Row).Rows
        'matching row based on Id ?
        m = Application.Match(rw.Cells(1).Value, wsDest.Columns("B"), 0)
        'if we didn't get a match then we add a new row
        If IsError(m) Then m = wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row 'new row
        rw.Copy wsDest.Cells(m, "A") 'copy row
    Next rw
    
    wb.Close False 'no save
        
End Sub
any suggestion guy to do that
 
thanks it works , but how can I overcome problem coping repeatedly to the bottom just I want clear before brings data (just should replace data)
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Assuming your destination sheet has a heading in row 1 and the data starting in row 2 and the columns are "A:E" then adding this before the For loop should clear the previous content.

VBA Code:
    With wsDest
        .Range("A2:E" & .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row).ClearContents
    End With
 
Upvote 0
thanks again but I face another problem , if I have new data in file where run the macro from it and not existed for the selected file will delete all of new data in file where run the macro from it . actually it shouldn't . should keep them .
 
Upvote 0
I think I understand what you are saying. Try replacing the whole of the code from For Each rw .... through to Next rw, with the below:
Not the match line is from your original code and seems unlikely.
Since you are copying from A:E to A:E then the match should be on the same column.
So if it is column B change the reference "1" to "2", if it is on Column "A" change the "B" to "A".

Rich (BB code):
    For Each rw In wsCopy.Range("A2:E" & wsCopy.Cells(Rows.Count, "A").End(xlUp).Row).Rows
        'matching row based on Id ?
        m = Application.Match(rw.Cells(1).Value, wsDest.Columns("B"), 0)
        'if we didn't get a match then we add a new row
        If IsError(m) And rw.Cells(1).Value <> "" Then
            m = wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row 'new row
            rw.Copy wsDest.Cells(m, "A") 'copy row
        End If
    Next rw
 
Upvote 0
I've found the problem is matching row based on Id (B) .there are duplicates ID so I change to column A
then will replace data and keep the data are existed in file run macro from it and not existed in imported data file
based on post#10 you've solved my problem .
many thanks for your assistance .:)
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top