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 wrote:

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

--

Dave Peterson