Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    2

    Unanswered: Run-time Error '1004' in Excel 2010

    Hi i am new to Excel and i am doing a spreadsheet system of stock control for a college project but i keep hitting an Run-time Error '1004' Unable to get the VLookup property of the WorksheetFunction Class.

    here is the current code for the login button.
    Code:
    Private Sub Login_Click()
    Dim Username, Password As String
    
    Username = UsernameT.Text
    Password = PasswordT.Text
    
    Sheets("Accounts").Visible = True
    
    Set Users = Sheets("Accounts").Range("A6:B56")
    Set Pass = Sheets("Accounts").Range("B6:C56")
    
    Dim Usr As Variant
    Dim Pwd As Variant
    
    Usr = Application.WorksheetFunction.VLookup(Username, Users, 2)
    Pwd = Application.WorksheetFunction.VLookup(Password, Pass, 2)
    
    If IsError(Usr) = False Then
        If Application.WorksheetFunction.VLookup(Username, Users, 2, False) = Password Then
            MsgBox "Login Successful.", vbInformation, "Success"
            Sheets("Main Menu").Range("a2") = Username
            Unload LoginForm
    Else
        MsgBox "Login Failed.", vbCritical, "Failed"
        End If
    End If
    
    Sheets("Accounts").Visible = False
    Unload LoginForm
    End Sub
    if anyone can tell me why it is giving me this error that would be nice and also help me fix it so i can learn from what mistake that i have made if there is any.

    It did not give me this error when i take out the false in the VLookup after the IsError line but then it still logs you in to the system. (at current time logging in to the system notice is the cell A2 on the main menu sheet)

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Which line is giving the error?

    Have you stepped through it to determine that the values in the variables are valid at each stage of execution?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Feb 2013
    Posts
    2
    Usr = Application.WorksheetFunction.VLookup(Username, Users, 2)
    Pwd = Application.WorksheetFunction.VLookup(Password, Pass, 2)
    If Application.WorksheetFunction.VLookup(Username, Users, 2, False) = Password Then

    Those are the lines that are failing but not all at once.
    I have gone through all the lines to make sure it is valid but soon as it hits one of these lines it gives me the error.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Try replacing the Range variables with strings that define them. That's how I got this to work, some years ago.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •