I would identify the matches on the sheet by using MATCH.

Put a formula in sheet1  cell B1 like =MATCH(A1,Sheet2!$A$1:$A$7,0) you will
get the row number of the corresponding value (or #N/A if there is no match).
You can then use the row number in the macro.

This is speculation, but ...

The "on error" fires and you jump to the "notfound" label. At this point,
you are in "error handler" mode. You then loop  and potentially have another
error. But you're already in error  mode, so a further error will not cause
the jump.

You could try putting an "on error goto 0" in the "notfound" code. That
might fix things.

Otherwise, you might need to exit from the loop and/or the sub to clear
error mode.

Speculation only - I could easily be completely off track.

Trust something to do with Find.
If it cannot find its target, it returns NOTHING

As suspected, the following code  works.

Sub test()
i = 2
Do While i < 40000

curr_lot_no = Sheet1.Cells(i, 1).Value
Sheet2.Select
On Error Resume Next 'GoTo notfound
If Not Sheet2.Range(Cells(1, 1), Cells(50000, 1)).Find(what:=curr_lot_no,
AFTER:=[A1], SearchDirection:=xlNext, searchorder:=xlByRows) Is Nothing Then
curr_lot_nbr_row_no = Sheet2.Range(Cells(1, 1), Cells(50000,
1)).Find(what:=curr_lot_no, AFTER:=[A1], SearchDirection:=xlNext,
searchorder:=xlByRows).Row

Sheet1.Cells(i, 4).Value = Sheet2.Cells(curr_lot_nbr_row_no, 4).Value
End If
notfound: i = i + 1
Loop
End Sub