IF range未返回所需值：Excel单元格自动同步需求技术求助

Got it, let's get this working for you! You'll need to use a Worksheet_Change event in Excel VBA—this triggers automatically whenever cells on the worksheet are edited, which is perfect for your requirement. Here's the step-by-step fix:

Step 1: Add the Code to Your Worksheet Module #

1. Press Alt + F11 to open the VBA Editor.
2. In the left-hand Project Explorer, double-click the worksheet where you want this functionality (e.g., Sheet1).
3. Paste the following code into the code window:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Define the range we're monitoring: A1 to A100
    Dim watchRange As Range
    Set watchRange = Me.Range("A1:A100")
    
    ' Only run code if the changed cells are within our target range
    If Not Intersect(Target, watchRange) Is Nothing Then
        ' Disable events temporarily to avoid infinite loops when updating column B
        Application.EnableEvents = False
        
        Dim cell As Range
        ' Loop through each modified cell in the monitored range
        For Each cell In Intersect(Target, watchRange)
            ' Check if the cell contains "Product" (case-insensitive match)
            If UCase(cell.Value) = "PRODUCT" Then
                ' Set corresponding B cell to 1
                cell.Offset(0, 1).Value = 1
            Else
                ' Clear the B cell if "Product" is removed or not present
                cell.Offset(0, 1).ClearContents
            End If
        Next cell
        
        ' Re-enable events so future edits work as expected
        Application.EnableEvents = True
    End If
End Sub

Key Details to Note #

• Case Insensitivity: The UCase function makes the check case-insensitive (so "product", "PRODUCT", etc., all trigger the 1 in column B). If you want an exact case match, remove UCase from both sides (change to cell.Value = "Product").
• Multiple Cell Edits: The code handles bulk changes (like pasting "Product" into multiple A cells at once) since it loops through each modified cell.
• Event Disabling: We turn off Application.EnableEvents before updating column B to prevent the code from running again when we modify column B (which would cause an infinite loop). We re-enable it right after to ensure future edits work.

Testing the Code #

• Type "Product" into any cell in A1:A100—you’ll see the corresponding B cell auto-fill with 1.
• Delete "Product" from the A cell, and the B cell will clear immediately.
• Try pasting multiple values into column A; the code will update column B correctly for all affected rows.

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