Unable to get Vlookup Property of WorksheetFunction class error

[cfcanter]

I am getting an error message "Unable to get Vlookup Property of WorksheetFunction class". Below is the code I am trying to use. I have just about every reference added that I can think of. Does anyone have an idea of what I am doing wrong? Please help, I'm desperate to turn a 3 day task into a 1/2 hour task.

thanks
c2

Global x, y, iComponentRow, iThiscolumn As Integer
Global dDemand As Double
Global sPartAddress As String
Global strStartMatl As String
Global aryComponent(10) As String

Sub GO()
Call FindPart
Call LoadArry
Call locateComponent
End Sub



Sub FindPart()
'
sFindCell = ActiveCell.Value
sPartAddress = ActiveCell.Address
Sheets("BKLOGTHRUDEC31050702 (3)&quot.Select
Columns("A:A&quot.Select
Range("A17&quot.Activate
Selection.Find(What:=sFindCell, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
End Sub

Sub LoadArry()

x = 1
ActiveCell.Offset(0, 4).Select
Do Until ActiveCell.Value = ""
aryComponent(x) = ActiveCell.Value
x = x + 1
ActiveCell.Offset(0, 1).Select
Loop
Sheets("mrp&quot.Select
Range(sPartAddress).Select
End Sub

Sub locateComponent()
dDemand = ActiveCell.Offset(0, 1).Value
ActiveCell.Offset(0, 3).Select
iThisRow = ActiveCell.Row
iThiscolumn = ActiveCell.Column
iComponentRow = -1 * (ActiveRow - 4)
For y = 1 To x
Do Until Cells(iComponentRow, iThiscolumn).Value = "END"

If Cells(iComponentRow, iThiscolumn).Value = aryComponent Then

End If
iThiscolumn = ActiveCell.Column + 1
ActiveCell.Offset(0, 1).Select
Loop
Range(sPartAddress).Select
ActiveCell.Offset(0, 3).Select
iThiscolumn = ActiveCell.Column
Next y
End Sub

Sub Formula1()
Dim dTestValue As Double

' error is on this line
iComponentValue = Application.WorksheetFunction _
.VLookup(aryComponent, "'wafer inv'A:S", 19, False)

dTestValue = ActiveCell.Value
If dTestValue >= dDemand Then
ActiveCell.Value = dDemand
Else
If dTestValue < dDemand And dTestValue > 0 Then
ActiveCell.Value = dTestValue
Else
ActiveCell.Value = &quot;xxx&quot;
End If
End If


End Sub Craig Canter
Cost Accountant
Sipex
Milpitas, CA

 

[Sawatzky]

dear cf,

first of all, Do you have set

option explicit

on the very start of your program??
if not do so.

next question what value is y when the error occurs??


I assume their is a problem with the scope of your variables and in consequence with the value of 'y'

HTH
regards astrid

 

[Sawatzky]

dear craig,

first of all, Do you have set

option explicit

on the very start of your program??
if not do so.

next question what value is y when the error occurs??


I assume their is a problem with the scope of your variables and in consequence with the value of 'y'

HTH
regards astrid

 

[AirZebra]

Hi,

I had a similar problem and rmikesmith told me to remove the worksheetfunction word, so it would end up looking like

iComponentValue = Application.VLookup(aryComponent(y), "'wafer inv'A:S", 19, False)

It worked for me on thread 707-269117

Regards,

alan

 

[xlbo]

The other way round should work as well. Some of the functions don't like the doublecall application.worksheetfunction.

Use either application.vlookup or worksheetfunction.vlookup

HTH
Geoff