VBA VLookup Function

[Andrew01]

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.

[Bob Phillips]

Try this
[vba] Price = Application.WorksheetFunction.VLookup(CLng(TimeInput), Range("J15:V50"), 3, False)
[/vba]

[Andrew01]

Still getting the same error? Any alternatives? Thanks.

[Andrew01]

The problem is that I'm looking for a Time data type and the lookup function doesnt seem to accept it

[Bob Phillips]

That is why I converted it to a long.

[Andrew01]

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!

[Teeroy]

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