# Thread: Code to match reference numbers

1. ## 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

2. 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. 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. 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)

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. 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. 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. Hi Steve

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

Howard

8. 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
•