請參考
VBA 找下一筆的官方教學

我抄上面的教學
寫了 TypeOne 和 TypeTwo 兩種用法
看你喜歡那一個就拿去用

'只用 SetOnePrice 就可以設定所有拍賣價格
Sub TypeOne()
    For nI = 3 To 10
        sCodeKind = Range("A" & nI).Value
        sPersonNo = Range("B" & nI).Value
        sPieces = Range("C" & nI).Value
        sTotalWeight = Range("D" & nI).Value
        sBidPrice = Range("E" & nI).Value
        Call SetOnePrice(sCodeKind, sPersonNo, sPieces, sTotalWeight, sBidPrice)
    Next nI
End Sub
'用 FindNext 找出並設定數值
Sub TypeTwo()
    Call FindAll("F1", "005", 1, 20)
End Sub
'找到第一筆沒有符合條件並且沒有拍賣價格的, 把拍賣價格設定上去
Sub SetOnePrice(ByVal CodeKind, ByVal PersonNo, ByVal Pieces, ByVal TotalWeight, ByVal bidPrice)
    Dim Found As Range, Firstfound As String
    Dim rngSearch As Range
    Dim Criteria As Variant
    
    Set rngSearch = Sheets("Sheet1").Range("G:G")
            
    Set Found = rngSearch.Find(What:=CodeKind, _
                               LookIn:=xlValues, _
                               LookAt:=xlWhole, _
                               SearchOrder:=xlByRows, _
                               SearchDirection:=xlNext, _
                               MatchCase:=False)
        
    If Not Found Is Nothing Then
        
        Firstfound = Found.Address
        
        Do
            If Found.EntireRow.Range("H1").Value = PersonNo And _
               Found.EntireRow.Range("I1").Value = Pieces And _
               Found.EntireRow.Range("J1").Value = TotalWeight And _
               Found.EntireRow.Range("K1").Value = "" Then
               
               Debug.Print Found.Address, bidPrice
               Found.EntireRow.Range("K1").Value = bidPrice
               Exit Do
            End If 'Match found
            
            Set Found = rngSearch.FindNext(After:=Found)
            If Found.Address = Firstfound Then Set Found = Nothing
            
        Loop Until Found Is Nothing
        
    End If
'    If Not Found Is Nothing Then
'        Debug.Print "Found"
'    Else
'        Debug.Print "Nothing matched all four criteria. ", , "No Match Found"
'    End If
End Sub
'找出所有符合條件的, 然後去目的檔搜尋並且設定拍賣價格
Sub FindAll(ByVal CodeKind, ByVal PersonNo, ByVal Pieces, ByVal TotalWeight)
    Dim Found As Range, Firstfound As String
    Dim rngSearch As Range
    Dim Criteria As Variant
    
    Set rngSearch = Sheets("Sheet1").Range("A:A")
    
    Set Found = rngSearch.Find(What:=CodeKind, _
                               LookIn:=xlValues, _
                               LookAt:=xlWhole, _
                               SearchOrder:=xlByRows, _
                               SearchDirection:=xlNext, _
                               MatchCase:=False)
        
    If Not Found Is Nothing Then
        
        Firstfound = Found.Address
        
        Do
            If Found.EntireRow.Range("B1").Value = PersonNo And _
               Found.EntireRow.Range("C1").Value = Pieces And _
               Found.EntireRow.Range("D1").Value = TotalWeight Then
               
               bidPrice = Found.EntireRow.Range("E1").Value
               'Debug.Print Found.Address, bidPrice
               Call SetOnePrice(CodeKind, PersonNo, Pieces, TotalWeight, bidPrice)
               'Exit Do
            End If 'Match found
            
            Set Found = rngSearch.FindNext(After:=Found)
            If Found.Address = Firstfound Then Set Found = Nothing
            
        Loop Until Found Is Nothing
        
    End If
    
    If Not Found Is Nothing Then
        Debug.Print "Found"
    Else
        Debug.Print "Nothing matched all four criteria. ", , "No Match Found"
    End If
End Sub