"Unable to get the VLookup property of the WorksheetFunction class

[Ayo]

I can't figure out why I am get the above error when i run the code below.
The error occurs on the first line after the "For" statement i.e.,
c.Offset(0, 3) = Application.WorksheetFunction.VLookup(c, casprRange, 45,
False)

Option Explicit
Dim c As Range, cl As Range, startingCell As Integer, i As Integer, l As
Integer

Sub prepSites()
Dim ClastRow As Integer, siteCount As Integer, SiteTable As Integer,
NEDA_SiteList As Integer
Dim rw As Integer, CurrRow As Integer, testcount As Integer
Dim casprRange As Range

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''' CALCULATE THE NUMBER OF ROWS IN THE "National Export Dynamic Activit"
TAB AND '''''''''''
'''''' THE NUMBER OF ACTIVE SITES
''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
NEDA_SiteList = Worksheets("National Export Dynamic
Activit").Cells(Rows.Count, "R").End(xlUp).Row
SiteTable = Worksheets("Site Table").Cells(Rows.Count, "B").End(xlUp).Row
Set casprRange = Sheets("National Export Dynamic Activit").Range("R2:ET" &
NEDA_SiteList)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''' FIND THE NUMBER OF MS030 2008 ACTUALIZED SITES IN THE 85 NSB SITES
TABLE ''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For Each c In Worksheets("Site Table").Range("B2:B" & SiteTable).Cells
c.Offset(0, 3) = Application.WorksheetFunction.VLookup(c,
casprRange, 45, False)
c.Offset(0, 4) = Application.WorksheetFunction.VLookup(c,
casprRange, 46, False)
c.Offset(0, 5) = Application.WorksheetFunction.VLookup(c,
casprRange, 51, False)
c.Offset(0, 6) = Application.WorksheetFunction.VLookup(c,
casprRange, 52, False)
c.Offset(0, 7) = Application.WorksheetFunction.VLookup(c,
casprRange, 81, False)
c.Offset(0, 8) = Application.WorksheetFunction.VLookup(c,
casprRange, 82, False)
c.Offset(0, 9) = Application.WorksheetFunction.VLookup(c,
casprRange, 132, False)
c.Offset(0, 10) = Application.WorksheetFunction.VLookup(c,
casprRange, 133, False)
Next c

End Sub

 

[Bernie Deitrick]

Ayo,

Your code worked fine for me. Make sure that you don't have any missing references in your project,
a frequent cause of unrelated errors....

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

application.worksheetfunction.vlookup() will cause an error if there is no
match.

Try:

c.Offset(0, 3) = Application.VLookup(c, casprRange, 45, False)

(dropping the .worksheetfunction)

 

[Ayo]

Thanks.

application.worksheetfunction.vlookup() will cause an error if there is no
match.

Try:

c.Offset(0, 3) = Application.VLookup(c, casprRange, 45, False)

(dropping the .worksheetfunction)

 

[Ayo]

Thanks.

Ayo,

Your code worked fine for me. Make sure that you don't have any missing references in your project,
a frequent cause of unrelated errors....

HTH,
Bernie
MS Excel MVP