• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA copy

Niranjan1979

New Member
My Sheet 1 has some records and Sheet 2, I need a macro to update sheet1 using sheet 2. I want to update only the one highlited in yellow. The column code ie. first column may be same for a few rows. Only matching records in sheet 1 to be updated. I cant add any new columns in sheet1 . with the existing i have to get the data.
 

Attachments

Last edited:

mohadin

Active Member
Hi
If I understand correctly
Try
Code:
Sub test()
    Dim a As Variant, lr, i, x
    a = Sheets("sheet2").Range("A4").CurrentRegion
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a)
            If a(i, 1) <> 0 Then
                If Not .exists(a(i, 1) & a(i, 2)) Then
                    .Add a(i, 1) & a(i, 2), Array(a(i, 3), a(i, 5), a(i, 6), a(i, 7))
                End If
            End If
        Next
        a = Sheets("sheet1").Range("A4").CurrentRegion
        For i = 2 To UBound(a)
            x = a(i, 1) & a(i, 2)
            If .exists(x) Then
                a(i, 3) = .Item(x)(0): a(i, 5) = .Item(x)(1)
                a(i, 6) = .Item(x)(2): a(i, 7) = .Item(x)(3)
            End If
        Next
        Sheets("sheet1").Range("a4").Resize(UBound(a), UBound(a, 2)) = a
    End With
End Sub
 
Top