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