如何使用VLOOKUP函数改写VBA会员查询代码并修复运行问题？

我正在编写VBA代码实现以下功能：点击Sheet1（inq）中的CommandButton2时，弹出输入框让用户输入会员编号，从Sheet4（member）的A4:B12区域查询对应会员姓名。原代码可正常运行，但改用VLOOKUP函数的版本无法正常工作，请求帮忙修正（新手，若有错误请见谅）。

原可运行代码 #

Private Sub CommandButton2_Click()
    Dim memberlists As Range
    Dim memberfinder As String
    Dim searchCell As Range
    Dim searchValue As Variant
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("member")
    Set memberlists = ws.Range("A4:B12")
    memberfinder = InputBox("Input your member number", "MemberFinder", "Example:100123")
    
    On Error Resume Next
    Set searchCell = memberlists.Columns(1).Find(memberfinder, LookIn:=xlValues, LookAt:=xlWhole)
    On Error GoTo 0
    
    If searchCell Is Nothing Then
        MsgBox "No member found. Please register first"
    Else
        searchValue = searchCell.Offset(0, 1).Value
        MsgBox "Member ID: " & memberfinder & vbNewLine & "Member Name: " & searchValue
    End If
End Sub

无法正常运行的VLOOKUP版本代码 #

Private Sub CommandButton2_Click()
    Dim memberlist As Range
    Dim memberfinder As String
    Dim searchValue As Variant
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("member")
    Set memberlist = ws.Range("A2:B12")
    memberfinder = InputBox("Input your member number", "MemberFinder", "Example:100123")
    
    searchValue = Application.VLookup(memberfinder, memberlist, 2, False)
    
    If IsError(searchValue) Then
        MsgBox "No member found. Please register first"
    Else
        MsgBox "Member ID: " & memberfinder & vbNewLine & "Member Name: " & searchValue
    End If
End Sub

会员列表截图 #

修正方案及代码 #

你的VLOOKUP版本主要有两个问题：

1. 查找区域错误：原代码用的是A4:B12，但VLOOKUP版本写成了A2:B12，超出了实际会员数据范围
2. 数据类型不匹配：截图中会员编号是数字，而InputBox返回的是字符串，直接用字符串查找数字会导致匹配失败

修正后的代码：

Private Sub CommandButton2_Click()
    Dim memberlist As Range
    Dim memberfinder As Variant
    Dim searchValue As Variant
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("member")
    ' 修正查找区域为A4:B12，和原代码一致
    Set memberlist = ws.Range("A4:B12")
    
    memberfinder = InputBox("Input your member number", "MemberFinder", "Example:100123")
    
    ' 处理用户取消输入或输入空值的情况
    If memberfinder = "" Then Exit Sub
    
    ' 将输入转为数字类型，匹配表格中的会员编号格式
    If IsNumeric(memberfinder) Then
        memberfinder = CLng(memberfinder)
    End If
    
    ' 使用VLOOKUP查找，严格匹配
    searchValue = Application.VLookup(memberfinder, memberlist, 2, False)
    
    If IsError(searchValue) Then
        MsgBox "No member found. Please register first"
    Else
        MsgBox "Member ID: " & memberfinder & vbNewLine & "Member Name: " & searchValue
    End If
End Sub

修改说明 #

• 把查找区域改回A4:B12，确保覆盖正确的会员数据范围
• 增加空输入判断，避免用户取消操作时触发错误
• 将输入的会员编号转为数字类型（CLng），和表格中的数字类型会员编号匹配，解决查找不匹配的问题
• 保留原有的错误判断逻辑，确保未找到时提示正确信息

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