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" & 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.

StackExchange Code Review Q#86516, answer score: 4