VBA实现按卡车/拖车查找对应驾驶员、日期及里程表值求助

Hey there! Let's work through your truck equipment tracker problems step by step. I see you've got the core logic to find the last occurrence of a truck, but the Offset assignment is reversed, and you're dealing with unwanted listbox changes. Let's sort this out.

Key Issues in Your Current Code #

• Reversed Value Assignment: You're trying to set the cell's value to the textbox (searchTerm.Offset(0, 1).Value = tbPrevTruckDriver) instead of the other way around. That's why your textboxes aren't populating, and this reversed write is probably modifying your source data (which explains the listbox changes).
• Incorrect Offset Mapping: You need to align the Offset values to the right columns:
  • D列（卡车）→ Offset(0,1) = E列（驾驶员）
  • D列 → Offset(0,-1) = C列（日期）
  • D列 → Offset(0,3) = G列（里程表）
• Overly Broad Error Handling: Your current handler triggers even when no error occurs, which creates unnecessary confusion.

Corrected Code #

Here's the revised code with fixes and clear comments:

Private Sub btnLoadInfo_Click()
    Dim DataSH As Worksheet
    Dim searchTerm As Range
    Dim searchValue As String
    
    ' Error handling setup
    On Error GoTo errHandler
    
    ' Speed up execution and prevent screen flicker
    Application.ScreenUpdating = False
    
    Set DataSH = Sheet1
    searchValue = cboTrucks.Value ' Get search term from your dropdown
    
    ' Find the LAST occurrence of the truck/trailer in column D
    Set searchTerm = DataSH.Range("D1:D999").Find( _
        what:=searchValue, _
        searchorder:=xlByRows, ' Explicit vertical search (default, but clearer)
        searchdirection:=xlPrevious, _
        LookIn:=xlValues ' Ensure we search cell values, not formulas
    )
    
    If searchTerm Is Nothing Then
        MsgBox "No records found for this truck/trailer", vbInformation, "Search Result"
    Else
        ' Populate textboxes correctly (TEXTBOX = CELL VALUE, not the reverse!)
        tbPrevTruckDriver.Value = searchTerm.Offset(0, 1).Value ' Pull driver from E column
        tbLastDate.Value = searchTerm.Offset(0, -1).Value ' Pull date from C column
        tbOdometer.Value = searchTerm.Offset(0, 3).Value ' Pull odometer from G column
        
        ' Clear test cell address if needed
        Reg1.Value = ""
    End If
    
    ' Cleanup and exit if no errors
    Cleanup:
        Application.ScreenUpdating = True
        Exit Sub
    
    ' Error handling block
    errHandler:
        MsgBox "An Error has Occurred " & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Description: " & Err.Description & vbCrLf & _
               "Please notify the administrator", vbCritical, "Error"
        Resume Cleanup ' Ensure screen updating is re-enabled
End Sub

Extra Tips to Prevent Listbox Issues #

• Avoid Accidental Source Data Writes: The original reversed assignment was modifying your D/E column data, which likely feeds your listbox. The corrected code only reads from the source, so this should stop the unwanted changes.
• Use Dynamic Search Ranges: Instead of hardcoding D1:D999, use DataSH.Range("D1", DataSH.Cells(DataSH.Rows.Count, "D").End(xlUp)) to search only used rows—it's more efficient and avoids missing new data.
• Explicit Control References: Add Me. before control names (e.g., Me.tbPrevTruckDriver) to avoid ambiguity if you have duplicate control names elsewhere in your project.

内容的提问来源于stack exchange，提问作者N.Nonov