PDA

View Full Version : VBA VLookup Function



Andrew01
04-24-2012, 06:20 AM
Hi,
Im having trouble implementing a VLookup function into a macro. My Code:

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


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
04-24-2012, 06:32 AM
Try this
Price = Application.WorksheetFunction.VLookup(CLng(TimeInput), Range("J15:V50"), 3, False)

Andrew01
04-24-2012, 03:39 PM
Still getting the same error? Any alternatives? Thanks.

Andrew01
04-24-2012, 03:55 PM
The problem is that I'm looking for a Time data type and the lookup function doesnt seem to accept it

Bob Phillips
04-24-2012, 04:24 PM
That is why I converted it to a long.

Andrew01
04-24-2012, 04:25 PM
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
04-24-2012, 04:33 PM
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