Clash Royale CLAN TAG#URR8PPP

Find matching range & replace adjacent cell value — Excel VBA

I have two data sets that I need to compare--one that contains my master data set, and a second with corrected values for one variable in that data set.

For instance,

Data Set:

and so on.

However, the User ID column is incorrect and needs to be updated with values from a separate sheet:

and so on.

My objective is to find a match using the first two cells as criteria, then if a match is found, replace the User ID in the first sheet accordingly. Unfortunately, the original data set is much smaller (~3k rows) than the sheet where the corrections are coming from (~30k rows), so a simpler solution has not yet been identified.

My VBA skills are poor (typical, I know) and this is as far as I have come:

Sub FindReplace()
Dim lr As Long, i As Long
With Sheets("Data Set")
lr = .Range("A:B").End(xlUp).Row
For i = lr To 1
If IsNumeric(Application.Match(.Range("A1:B1").Value, Sheets("Values").Range("A1:B1").Value, 0)) Then
.Cells("C1").Value = Sheets("Values").Cells("C1")
End If
Next i
End With
End Sub


However, this doesn't do anything and I'm a bit stumped (and over my head). Ideally, I would appreciate some additional explanation of why the above doesn't work so that I might learn from my mistakes, but any help at all is infinitely appreciated.

Many thanks for the assistance!

+ds

2 Answers
2

As Alex stated, you don't need VBA, a VLookup will work just fine. You can concatenate both criteria into a third column on both sheets, then vlookup against that value.

Just make sure your concatenated column is at the left of the ID that you would like to retrieve.

Once you get your IDs updated, hard-code the formulas with a copy/pastespecial:values, and delete the concatenated column.

Without getting into building and testing this, I can see that you have a typo.

Sub FindReplace()
Dim lr As Long, i As Long
With Sheets("Data Set")
lr = .Range("A:B").End(xlUp).Row
**For i = lr To 1**
If IsNumeric(Application.Match(.Range("A1:B1").Value, Sheets("Values").Range("A1:B1").Value, 0)) Then .Cells("C1").Value = Sheets("Values").Cells("C1")
Next i
End With
End Sub


Should be

For i = 1 To lr


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.