Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > "Unable to get the VLookup property of the WorksheetFunction class
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Ayo Ayo is offline
external usenet poster
  
Posts: 489
Default "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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,441
Default "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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default "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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
Ayo Ayo is offline
external usenet poster
  
Posts: 489
Default "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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
Ayo Ayo is offline
external usenet poster
  
Posts: 489
Default "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




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Unable to set the XValues property of the Series class Ben Charts and Charting in Excel 7 December 7th 06 10:01 PM
Unable to set the Values property of the Series class rafael garcia Charts and Charting in Excel 1 September 25th 06 04:31 PM
Unable to set the formula property of the series class Malhar Charts and Charting in Excel 1 July 24th 06 05:50 PM
"Unable to set the Formula property of the Series class" with a tw PeterQ Charts and Charting in Excel 1 February 15th 06 07:37 PM
"Unable to get the NormSInv property of the WorksheetFunction clas David Roodman Excel Worksheet Functions 1 December 15th 04 12:15 PM


All times are GMT +1. The time now is 12:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement