Results 1 to 3 of 3

Thread: Using VLookup in VBA

  1. #1

    Using VLookup in VBA



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a form with a combo box, ComboBox1, that allows the user to select an existing employee from the range DATATBL ("a2500") on the worksheet HideEmployee. I then want to use that value to look up several other pieces of data from that employee record and display in the form. I have tried several code examples but keep coming up with error 424 Object required. I a looking for the proper way address the range in VLOOKUP function. Thank you for your help.

    Code:
    Private Sub ComboBox1_change()
         Dim cStatus as string, cClass as string, cEmp as string
         cEmp=Me!ComboBox1.Value
         ' cStatus=Application.Vlookup(cEmp, Sheet.hideemployee.range("a2:d500"), 4, False)
         cStatus=Excel.Application.WorksheetFunction.Vlookup(cEmp,Sheet.HideEmployee.Range("a2:d500"), 4, False)
         if cStatus="A" then
              Me!CheckBox1.Value=True
         else
              Me!CheckBox1.Value=False
         end if
    End Sub
    Last edited by Bob Phillips; 2014-05-30 at 08:59 AM. Reason: Added VBA tags

  2. #2
    See if this is any better

    Code:
    Private Sub ComboBox1_change()
    Dim cStatus As String, cClass As String, cEmp As String
        cEmp = Me.ComboBox1.Value
        cStatus = Application.VLookup(cEmp, Sheets("HideEmployee").Range("A2:D500"), 4, False)
        Me.CheckBox1.Value = cStatus = "A"
    End Sub
    Last edited by Bob Phillips; 2014-05-30 at 03:05 PM.

  3. #3
    Thank you Bob. This worked perfectly.

    Quote Originally Posted by Bob Phillips View Post
    See if this is any better

    Code:
    Private Sub ComboBox1_change()Dim cStatus As String, cClass As String, cEmp As String
        cEmp = Me.ComboBox1.Value
        cStatus = Application.VLookup(cEmp, Sheets("HideEmployee").Range("A2:D500"), 4, False)
        Me.CheckBox1.Value = cStatus = "A"
    End Sub

Posting Permissions

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