Excel .find Macro to write data to archive I would like to speed up this piece of code. The macro gets a search variable from the worksheet "RAW_DATA_ARCHIVE" and then it should find it in the worksheet "RAW DATA". When it finds accordance then the value of column A from worksheet "RAW DATA" will be written in the last column of "RAW_DATA_ARCHIVE".

 For i = 2 To ws1LRow
    SearchString = ws1.Range("A" &amp; i).Value
    Set aCell = ws2.Columns(5).Find(What:=SearchString, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
    'If match found
    If Not aCell Is Nothing Then
        ws1.Cells(i, 80).Value = ws2.Cells(aCell.Row, 1).Value

    End If
Next
If I understand correctly what you're doing, then VBA is simply not the right tool for this task. Excel has a built-in VLOOKUP function specifically for this purpose - try something like this in column 80 of the RAW_DATA_ARCHIVE sheet:

=IFERROR(VLOOKUP(A2,'RAW DATA'!E:E,1,FALSE),"")




That said, you did post some VBA code, albeit just a loop. Here are some thoughts, mostly on naming style:


ws1 and ws2 would probably be better off as sourceSheet and targetSheet.
ws1LRow would be more meaningful as simply lastRow
i is usually fine as a loop counter, but here it has a clear meaning: targetRow
aCell is really a searchResult


Using such meaningful names instantly makes it easier to read the code, which makes it easier to maintain/modify without breaking anything.

It also eliminates the need for explanatory comments:


'If match found
If Not aCell Is Nothing Then



vs.

If Not searchResult Is Nothing Then




A number of things explain why your code is slow.


You're using procedural code instead of built-in worksheet functions
You're writing to cells in a loop, presumably with Application.ScreenUpdating = True and Application.Calculation = xlCalculationAutomatic, which means many things happen every time you write a value into a cell.