Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,425
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Code to match reference numbers

    I have two sheets one called "old data" and one called "Data". The sheet data is imported

    I have written code to open a file which has been downloaded and copy this into a sheet called data

    I now need code that will compare the data on sheet called data with sheet old data. Where the ref in Co ml A on sheet “old data” matches sheet “Data” ,then copy the data from sheet “old data” into the sheet “Data” so that the data containing the same reference is overwritten in the sheet data’


    Your assistance in this regard is most appreciated
    Attached Files Attached Files
    Last edited by HowardC; 2013-12-14 at 05:10.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Howard, you are again asking for code, you should start being able to write yourself. Use the macro recorder for the brunt of the code. To do the looping, you must decide if you want to loop through all the records in "old data" or all the records in "data". I would loop through the smaller dataset. [you can choose this at runtime if desired]

    Go through each row in Col A, then use MATCH to find in the other dataset. That will give you the row of the match. Thus you have the copy row and paste row and for each match the row can be copied into the other.

    Post back with questions about the logic.

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,425
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Steve

    I have made an attempt to match the data on sheet2 (old data) to sheet3 (data) and where the data in Col A matches and then to copy the matched data from sheet2 on to sheet3 i.e. overwrite the matched data on sheet3 ("data")


    However the matched data does not copy over. It would be appreciated if you could assist me
    Code:
     Sub Match_Copy()
       Dim i, j, NumOfRows1, NumofRows2 As Long
    NumOfRows1 = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
    NumofRows2 = Sheets(3).Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To NumOfRows1
    For j = 2 To NumofRows2
    If Sheets(2).Cells(i, 1) = Sheets(3).Cells(j, 1) Then
    Sheets(3).Cells(i, 1) = Sheets(2).Cells(j, 1)
    End If
    Next j
    Next i
    End Sub

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You are not copying any data. The line
    Sheets(3).Cells(i, 1) = Sheets(2).Cells(j, 1)

    Only overwrites column A, which does not do much, since you only do it in the rows where the they are equal. Perhaps you want something more like:
    Sheets(2).Range("A" & j & ":BD" & j).Copy Sheets(3).Cells(i, 1)

    Some other comments:
    The line
    Dim i, j, NumOfRows1, NumofRows2 As Long

    dims ONLY the NumofRows2 as long the rest are variants. You need to explicitly list the AS for each

    I would not use Sheets(2) and Sheets(3). The order of sheets can change by someone moving or inserting sheets, so the sheets may not be what you expect. [In fact if you had checked, your "sheet(3)" is not "data" as you expect, it is a sheet called "macro". The Data worksheet is sheets(4). I recommend something more like:
    Dim wksOld as worksheet
    Dim wksData as worksheet

    Set wksOld = Worksheets("OldData")
    Set wksData = Worksheets("Data")

    And then use wksOld.Cells... or wksOld.range...

    The code becomes more clear and more explicit

    as I mentioned instead of looping and checking through both sets (you even continue to look after you found a match) looping through the smaller set and using match to find the row will be much more efficient.

    Steve

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,425
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the pointers. I'm getting closer to the desired result.

    When activating the macro, I get compile error "Invalid next control variable reference" and the following code is highlighted. It would be appreciated if you would correct the error in the code

    Code:
     next r1
    Code:
     Sub Match_Data()
    Dim rl As Range, r2 As Range, rRng As Range
     Dim lRw As Long
    Dim WksOld As Worksheet
    Dim Wksdata As Worksheet
    Set WksOld = Worksheets("Old Data")
     Set Wksdata = Worksheets("Data")
    
     
        Set rRng = Wksdata.Cells(1, 1).CurrentRegion.Offset(1).Columns(1)
        
         
        For Each rl In rRng.Cells
             
            Set r2 = WksOld.Cells(1, 1).CurrentRegion.Columns(1).Find(rl.Value)
             
            If Not r2 Is Nothing Then r2.EntireRow.Copy rl
        Next r1
    End Sub

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Check your variables, the for each uses rl(are - el) and the Next uses r1(are - one)...
    [This is one reason for using Option Explicit, it would have indicated that r1(are - one) is not defined.]

    Steve

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,425
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for pointing this out to me. Macro works perfectly now

    Howard

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You are very welcome. Glad I could help and happy you got most of it worked out yourself.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •