Searching values of range X in range Y This allows me to search each cell J4:J6525 in range K4:K6525. If it is found it will paste the values in Offset(0, 2) (column M).

I run it for files with more than 60000 lines, and sometimes much bigger. It takes a long time to complete. How can I make this run faster?

Sub Finder()

Dim WhereToSearch As Range, WhatToSearch As Range, ValueFound As Range, ValueSearched As Range

Set WhereToSearch = ActiveSheet.Range("K4:K6525")
Set WhatToSearch = ActiveSheet.Range("J4:J6525")


For Each ValueSearched In WhatToSearch

Set ValueFound = WhereToSearch.Find(What:=ValueSearched, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not ValueFound Is Nothing Then


ValueFound.Offset(0, 2) = ValueFound


End If
Next 
End Sub
It's hard to beat the performance of standard Excel WorksheetFunctions.
This will find the matches.


  Range("M4:M6525").Formula = "=IF(ISNA(MATCH(J4,$K$4:$K$6525,0)),"""",INDEX($K$4:$K$6525,MATCH(J4,$K$4:$K$6525,0), 1))"


Then if you want to remove the formulae use this


  Range("M4:M6525").Value =Range("M4:M6525").Value