ComboBox重复值更新故障：VBA代码无法批量更新同名字段

Let's break down what's going wrong with your current code and fix it to handle duplicate names in ComboBox9, updating all matching rows' column H with TextBox19's value.

Issues in Your Original Code #

• Reverse Match Logic: Your Application.Match check is backwards. You're showing "Record Not found" when the match is found, which is the opposite of what you need. We should use IsError() to detect when no match exists.
• Incorrect Offset Calculation: Column C to H is 5 columns apart (C=3, H=8; 8-3=5), but you used Offset(0,6) which points to column I instead of H.
• Broken Assignment Syntax: The line findvalue2.Offset(0, 6).Value = Me.TextBox19.Value = "" is invalid VBA—you can't chain assignments like that. This would never update the cell correctly.
• Unnecessary Exit Condition: Checking findvalue2.Offset(0, -1).Value = Me.TextBox19 (which checks column B) has nothing to do with your requirement, and it makes the loop exit early, skipping duplicate rows.

Corrected VBA Code #

Here's the fixed version that will update all rows where column C matches ComboBox9's selected value, setting column H to TextBox19's content:

Private Sub ComboBox9_Change()
    Dim sh As Worksheet
    Dim findValue As Range
    Dim firstAddress As String
    Dim targetName As String
    
    ' Set your worksheet (replace "Sheet1" with your actual sheet name)
    Set sh = ThisWorkbook.Worksheets("Sheet1")
    
    targetName = Me.ComboBox9.Value
    
    If targetName = "" Then Exit Sub
    
    ' Unprotect sheet first (we can re-protect at the end)
    sh.Unprotect "1234"
    
    ' Find the first occurrence of the name in column C
    Set findValue = sh.Range("C:C").Find(What:=targetName, LookIn:=xlValues, LookAt:=xlWhole)
    
    If Not findValue Is Nothing Then
        firstAddress = findValue.Address
        Do
            ' Update column H (5 columns offset from C) with TextBox19's value
            findValue.Offset(0, 5).Value = Me.TextBox19.Value
            
            ' Find the next occurrence
            Set findValue = sh.Range("C:C").FindNext(findValue)
        ' Loop until we're back to the first found address
        Loop While Not findValue Is Nothing And findValue.Address <> firstAddress
    Else
        ' No match found, show error
        MsgBox "Record Not found for this name", vbCritical
    End If
    
    ' Re-protect the sheet
    sh.Protect "1234"
    
    ' Clean up objects
    Set findValue = Nothing
    Set sh = Nothing
End Sub

Key Improvements #

1. Proper Match Detection: Uses Find and checks if the range exists, with a clear error message when no matches are found.
2. Full Duplicate Handling: The Do...Loop iterates through every occurrence of the name in column C, updating each corresponding H column cell.
3. Correct Offset: Offset(0,5) correctly targets column H from column C.
4. Clean Sheet Protection: Unprotects the sheet once at the start and re-protects it after all updates, instead of doing it inside the loop.
5. Clear Object Cleanup: Properly releases worksheet and range objects to avoid memory leaks.

If You Need to Update Only "Unupdated" Rows #

If your original requirement was to update only rows where column H is empty (instead of all matching rows), modify the loop to add a check:

Do
    ' Only update if column H is empty
    If findValue.Offset(0, 5).Value = "" Then
        findValue.Offset(0, 5).Value = Me.TextBox19.Value
    End If
    
    Set findValue = sh.Range("C:C").FindNext(findValue)
Loop While Not findValue Is Nothing And findValue.Address <> firstAddress

内容的提问来源于stack exchange，提问作者Harin