Consulting

Results 1 to 3 of 3

Thread: Run Time err 1004: Unable to get the vlookup property of the worksheet function class

  1. #1
    VBAX Regular
    Joined
    May 2010
    Posts
    11
    Location

    Run Time err 1004: Unable to get the vlookup property of the worksheet function class

    Hello,

    I am trying to do a vlookup on current worksheet compared to a range in another file.

    I get a "Run Time Error 1004: Unable to get the vlookup property of the worksheet function class".

    After some investigation, I found out that if there are some values that dont match up this could be a problem. In my case I will run into that situiation. But for testing purposes, I tried manipulating the data so that this doesnt happen. With no luck.

    Here is my code:

     
    Dim lastrow as Long
    
    lastrow = Sheets("Master List").Range("A1").End(xlDown).Row
    
    For i = 2 To lastrow
    Sheets("Master List").Cells(i, "B").Value = Application.WorksheetFunction.VLookup(Sheets("Master List").Cells(i, "A"), "'T:\Operations+Technology\NOC\Abuse Emails\[Customer Data for Abuse Emails.xls]Report'!$E$4:$G$60000", 2, False)
    Next i
    Thanks in Advance

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Maybe some values don't match. Try

    [vba]

    Const LOOKUP_TABLE AS string = _
    "'T:\Operations+Technology\NOC\Abuse Emails\[Customer Data for Abuse Emails.xls]Report'!$E$4:$G$60000"
    Dim lastrow as Long

    With Sheets("Master List")

    lastrow = .Range("A1").End(xlDown).Row

    For i = 2 To lastrow

    If Not IsError(Application.VLookup(.Cells(i, "B").Value, , LOOKUP_TABLE, 2, False)) Then

    .Cells(i, "B").Value = Application.VLookup(.Cells(i, "A"), LOOKUP_TABLE, 2, False)
    Else

    .Cells(i, "B").Value = ""
    End If
    Next i
    End With
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    That isn't going to work, you would need to evaluate the formula, and that always errors in my tests.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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