Excel VBA需求：VLOOKUP更新文本框，值不在范围时不显示价格

Hey there! The issue you're seeing happens because On Error Resume Next is sweeping the VLOOKUP failure under the rug—when the value isn't in the range, the code skips updating Price, leaving the old value stuck. Let's fix that with two clean approaches:

Approach 1: Check for Error Values Directly #

This method uses Application.VLookup (instead of WorksheetFunction.VLookup) which returns an error value when no match is found, so we can check it explicitly:

Private Sub ComboBox2_Change()
    Dim myRange As Range
    Dim lookupResult As Variant
    
    ' Start by clearing the Price textbox
    Price.Value = ""
    
    ' Define your lookup range
    Set myRange = Worksheets("cash").Range("BF:BH")
    
    ' Run the lookup (returns error if no match)
    lookupResult = Application.VLookup(ComboBox2.Value, myRange, 2, 0)
    
    ' Only update Price if we found a valid match
    If Not IsError(lookupResult) Then
        Price.Value = lookupResult
    End If
End Sub

Why this works: #

• We first empty Price so if the lookup fails, it stays empty instead of showing old data.
• Application.VLookup is forgiving—instead of throwing a runtime error, it returns #N/A as a variant, which we can check with IsError.
• We removed On Error Resume Next because we're handling the error case intentionally now, which avoids hiding other unexpected bugs.

Approach 2: Use Error Handling #

If you prefer traditional VBA error handling, this version uses a jump label to catch failures:

Private Sub ComboBox2_Change()
    Dim myRange As Range
    
    ' Clear Price upfront
    Price.Value = ""
    
    On Error GoTo NoMatchFound
    Set myRange = Worksheets("cash").Range("BF:BH")
    Price.Value = Application.WorksheetFunction.VLookup(ComboBox2.Value, myRange, 2, 0)
    
    ' Exit before hitting the error handler if lookup succeeds
    Exit Sub

NoMatchFound:
    ' Do nothing here—Price is already cleared
End Sub

Why this works: #

• If WorksheetFunction.VLookup can't find a match, it throws an error, and we jump to NoMatchFound.
• Since we cleared Price at the start, the textbox stays empty when no match exists.

Either approach will solve your problem—pick whichever fits your coding style better!

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