VBA中VLookup按邻列条件更新指定单元格异常问题求助

Hey there, let's work through this VBA problem step by step — your core goal is solid, we just need to tweak how the loop targets individual rows instead of the entire column.

What Was Wrong With the Original Code #

Let's break down why you were seeing those unexpected results:

• You were assigning the entire CHNGRNG range (all of column J) in every loop iteration. That meant every time a cell in column L matched "Trust for RCGA", the whole J column got overwritten with the VLookup result from row 2 — hence all matches showing the first result.
• There was no direct link between the current cell you were checking in column L and the corresponding row in column J.

Corrected Code #

Here's the revised version that targets only the specific J cell for each matching row, and leaves J values untouched when column L is "0":

Sub UpdateRCGAValues()
    Dim S1 As Worksheet
    Set S1 = Worksheets("Sheet1")
    Dim rcga As Worksheet
    Set rcga = Worksheets("RCGA")
    
    ' Get last row with data in Sheet1's column A (adjust if your data starts elsewhere)
    Dim tableRow As Long
    tableRow = S1.Range("A" & S1.Rows.Count).End(xlUp).Row
    
    Dim refCell As Range
    Dim lookupValue As Variant
    Dim rcgaLastRow As Long
    
    ' Get last row in RCGA sheet to keep the lookup range efficient
    rcgaLastRow = rcga.Range("A" & rcga.Rows.Count).End(xlUp).Row
    
    ' Loop through each cell in column L (starting from row 2)
    For Each refCell In S1.Range("L2:L" & tableRow)
        If refCell.Value = "Trust for RCGA" Then
            ' Grab the lookup value from column A in the SAME row as the current L cell
            lookupValue = S1.Range("A" & refCell.Row).Value
            
            ' Use Application.VLookup to handle missing matches gracefully
            Dim vLookupResult As Variant
            vLookupResult = Application.VLookup(lookupValue, rcga.Range("A2:P" & rcgaLastRow), 16, False)
            
            ' Only update J if a match was found
            If Not IsError(vLookupResult) Then
                S1.Range("J" & refCell.Row).Value = vLookupResult
            Else
                ' Optional: Add a note if no match exists (remove this line if you want to leave J blank)
                S1.Range("J" & refCell.Row).Value = "No match found"
            End If
        ' Do nothing when column L is "0" (as you requested)
        ElseIf refCell.Value = "0" Then
            ' Leave the existing J value as-is
        End If
    Next refCell
End Sub

Key Changes Explained #

• Targeted Row Updates: We use refCell.Row to get the current row number, then update only S1.Range("J" & refCell.Row) — this directly links the column L check to the correct J cell.
• Single Lookup Value: Instead of passing a whole range to VLookup, we grab the exact value from column A of the current row, so each lookup is specific to that row.
• Error Handling: Using Application.VLookup (instead of WorksheetFunction.VLookup) lets us check for missing matches with IsError(), so you won't get runtime errors when no match exists.
• Efficient Ranges: We calculate the last row for the RCGA sheet separately to avoid including blank rows in the lookup range.

Quick Performance Tip #

If you're working with a large dataset, add these lines at the start of the sub to speed things up:

Application.ScreenUpdating = False
Application.EnableEvents = False

And re-enable them at the end:

Application.ScreenUpdating = True
Application.EnableEvents = True

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