Looping For Duplicates In 2 Dynamic Columns (Odd Issue)

[broman5000]

I normally have no issue with this code, but i'm hoping someone can help me...definitely late on this for my job.

I have 2 columns (A and B) - See below just for an example... I have around 3000 rows of data. The headers will stay the same, but the values in column A and B will change week over week.

Column A is the master and will be updated manually by a different organization, but the amount of rows will change on both week over week.

It seems to work until row 1950 and then it doesn't...I'm not sure why...Please help!!!

Basically I was to loop through column B and check if it matches anywhere in column A and output "Match" in column C that correlates cell/row in column B

Here's my worksheet too.

I'm using the code below :

Sub routechange()
Dim CompareRange As Variant, To_Be_Compared As Variant, x As Variant, y As Variant, i As Long


Sheets("Unique Identifier list").Select
Range("B2").Select
Selection.End(xlDown).Select
Set To_Be_Compared = Range("B2:" & Selection.Address)
Range("A2").Select
Selection.End(xlDown).Select
Set CompareRange = Range("A2:" & Selection.Address)


i = 0
To_Be_Compared.Select

For Each x In Selection
For Each y In CompareRange
If x = y Then
Range("C2").Offset(i, 0).Value = "match"
i = i + 1
End If
Next y
Next x
End Sub





Master Unique Indicator As Of P201701
Weekly Run Unique Identifier


F00/00495362210:30
M37/00523240111:00


F00/00595362113:00
R40/00523240112:00


F01/002654454 6:30
M35/002232402 8:30


F01/003270805 3:00
R34/00423240211:00


F01/004954315 8:30
M28/00823250115:30


F01/004967309 9:00
T24/00523250112:00


F01/004969307 4:00
F31/00723250112:00


F01/00527831010:00
M36/009232601 8:30


F01/00696930510:15
R37/00923260110:00


F01/00727080813:30
W43/00523330111:30


F01/010812017 5:00
S44/00523330111:30


F01/06088604810:15
M20/00823330210:30


F01/07027700411:15
R20/00823330210:00

[offthelip]

Here try this, your indexing isn't correct:

Sub routechange2()
Dim CompareRange As Variant, To_Be_Compared As Variant, x As Long, y As Long, i As Long


Sheets(1).Select
alr = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
blr = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row


To_Be_Compared = Range("B1:B" & blr)
CompareRange = Range("A1:A" & alr)



For x = 2 To blr
For y = 2 To alr
If To_Be_Compared(x, 1) = CompareRange(y, 1) Then
Range("C2").Offset(x, 0).Value = "match"
End If
Next y
Next x


End Sub

[broman5000]

Thanks for answering...how are you defining your variables for alr and blr? I get a compile error as the variable isn't defined.

[offthelip]

They are longs

[broman5000]

got it. works perfectly. Thank you!