ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   "Unable to get the VLookup property of the WorksheetFunction class (https://www.excelbanter.com/excel-discussion-misc-queries/197811-unable-get-vlookup-property-worksheetfunction-class.html)

Ayo
"Unable to get the VLookup property of the WorksheetFunction class
 
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
"Unable to get the VLookup property of the WorksheetFunction class
 
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


"Ayo" wrote in message
...
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
"Unable to get the VLookup property of the WorksheetFunction class
 
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

Ayo
"Unable to get the VLookup property of the WorksheetFunction c
 
Thanks.

"Dave Peterson" wrote:

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

Ayo
"Unable to get the VLookup property of the WorksheetFunction c
 
Thanks.

"Bernie Deitrick" wrote:

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


"Ayo" wrote in message
...
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


All times are GMT +1. The time now is 10:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com