Consulting

Results 1 to 7 of 7

Thread: VBA VLookup Function

  1. #1
    VBAX Regular
    Joined
    Apr 2012
    Posts
    14
    Location

    VBA VLookup Function

    Hi,
    Im having trouble implementing a VLookup function into a macro. My Code:

    VBA:
    [VBA]Private Sub cmdCalculate_Click()

    Dim TimeInput As Date
    Dim Stock As String
    Dim Price As Double
    Dim PercentageChange As Double

    TimeInput = Range("A7")
    Stock = Range("B7")

    If Stock = "A" Then

    Price = Application.WorksheetFunction.VLookup(TimeInput, Range("J15:V50"), 3, False)
    MsgBox (Price)


    ElseIf Stock = "B" Then
    'Insert
    ElseIf Stock = "C" Then
    'Insert
    ElseIf Stock = "D" Then
    'Insert
    End If

    End Sub[/VBA]


    I get the following error message when I click the button:

    Runtime Error 1004:
    Unable to get the VLookup Property of the Worksheet Function class.
    Any Ideas would be greatly appreciated.
    Cheers.
    Last edited by xld; 04-24-2012 at 06:25 AM. Reason: Added VBA tags

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    Try this
    [vba] Price = Application.WorksheetFunction.VLookup(CLng(TimeInput), Range("J15:V50"), 3, False)
    [/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
    VBAX Regular
    Joined
    Apr 2012
    Posts
    14
    Location
    Still getting the same error? Any alternatives? Thanks.

  4. #4
    VBAX Regular
    Joined
    Apr 2012
    Posts
    14
    Location
    The problem is that I'm looking for a Time data type and the lookup function doesnt seem to accept it

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    That is why I converted it to a long.
    ____________________________________________
    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

  6. #6
    VBAX Regular
    Joined
    Apr 2012
    Posts
    14
    Location
    Just worked it out, had another column which displayed time values as general format and looked it up as a double data type.
    Thanks for your help!

  7. #7
    VBAX Mentor Teeroy's Avatar
    Joined
    Apr 2012
    Location
    Sydney, Australia
    Posts
    414
    Location
    Try using .Find instead of the worksheet function
    e.g.

    Dim found as Range

    found = Range("J15:V50").Find(What:=TimeInput, MatchCase:=False)
    price = posn.offset(0,2)

    You can also then test if TimeInput isn't found in the range.

    If found Is Nothing Then
    ' Do something if not found
    End if

Posting Permissions

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