运行时错误 1004 - 无法获取 WorksheetFunction 类的 VLookup 属性-解网

问：

这是我的代码

Private Sub CommandButton1_Click()

Dim Username As String
Dim Password As String
Dim LookupPassword As Variant
Dim LookupPermission As Variant



Username = user.Value
Password = pass.Value

LookupPassword = Application.WorksheetFunction.VLookup("Username", Range("UNT"), 3, 0)
LookupPermission = Application.WorksheetFunction.VLookup("Username", Range("UNT"), 4, 0)

If IsError(LookupPassword) Then
MsgBox "Error"
Exit Sub

ElseIf Password <> LookupPassword Then
MsgBox "Error"
Exit Sub

ElseIf LookupPermission = "View" Then
Application.Visible = True

ElseIf LookupPermission = "Admin" Then
Application.Visible = True

ElseIf LookupPermission = "DataEntry" Then
Application.Visible = True

Else
MsgBox "Error"

End If


End Sub

我编写代码从表（UNT）查找用户名、密码和权限，但我有此错误（运行时错误 1004 无法获取 vlookup）

Excel vlookup

Private Sub CommandButton1_Click()

    Dim Username As String
    Dim LookupPassword As String

    Username = user.Value

    On Error Resume Next
    LookupPassword = WorksheetFunction.VLookup(Username, Range("UNT"), 3, 0)

    If Err.Number <> 0 Then
        MsgBox "Error"
        Exit Sub
    End If
    On Error GoTo 0

    'proceed with code...

End Sub

选项 2：带和类型Application.VLookupIsError()Variant

Private Sub CommandButton1_Click()

    Dim Username As String
    Dim LookupPassword As Variant

    Username = user.Value

    LookupPassword = Application.VLookup(Username, Range("UNT"), 3, 0)

    If IsError(LookupPassword) Then
        MsgBox "Error"
        Exit Sub
    End If

    'proceed with code...

End Sub

注意：使用此方法时，如果不声明查找变量，它将失败，并显示“运行时错误'13'：类型不匹配”。As Variant

仅供参考：您可以使用单个函数而不是多个函数来使用该方法返回每个项目。例如：MatchVLookupRange.Cells

Private Sub CommandButton1_Click()

'Validate username
    Dim rg As Range, Username As String, rowId As Variant
    Set rg = Range("UNT")
    Username = user.Value

    rowId = Application.Match(Username, rg.Columns(1), 0)

    If IsError(rowId) Then
        MsgBox "Username not found"
        Exit Sub
    End If

'Validate password
    Dim Password As String, LookupPassword As String
    Password = pass.Value
    LookupPassword = rg.Cells(rowId, 3).Value

    If StrComp(Password, LookupPassword, vbBinaryCompare) <> 0 Then
        MsgBox "Incorrect password"
        Exit Sub
    End If

'Validate permission
    Dim LookupPermission As String
    LookupPermission = rg.Cells(rowId, 4).Value

    Select Case LookupPermission
        Case "View", "Admin", "DataEntry"
            Application.Visible = True
        Case Else
            MsgBox "Access denied"
    End Select

End Sub

我还在这里使用（而不是多个语句）来简化用户权限验证过程。干杯！Select CaseElseIf

上一个：如何将excel中的vlookup函数更改为python脚本，如图所示？

下一个：用于返回唯一值的下拉列表

运行时错误 1004 - 无法获取 WorksheetFunction 类的 VLookup 属性

Runtime Error 1004 - Unable to get the VLookup property of the WorksheetFunction class

评论