Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > 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.programming
Ayo Ayo is offline
external usenet poster
  
Posts: 489
Default Unable to get the VLookup property of the WorksheetFunction class
How do I go around this runtime error? I tried the following:

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < " " Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then
trueOnAirWS.Range("J" & reportCurrentRow & "") =
Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata,
7, False)
End If
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default Unable to get the VLookup property of the WorksheetFunction class
this issue is that VLOOKUP and other similar function raise an error when no
match is found. Wrap it in an error trap - personally, I do this within a
user defined function so that my error handling doesn't break, but not
everybody likes this ...

ON ERROR RESUME NEXT
result = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata,
7, False)
ON ERROR GOTO 0


"Ayo" wrote:

How do I go around this runtime error? I tried the following:

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < " " Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then
trueOnAirWS.Range("J" & reportCurrentRow & "") =
Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata,
7, False)
End If
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
Ayo Ayo is offline
external usenet poster
  
Posts: 489
Default Unable to get the VLookup property of the WorksheetFunction cl
Didn't work. still getting the error. This is the entire subroutine:
Private Sub cmdUpdate_Click()
Dim insiteWS As Worksheet, trueOnAirWS As Worksheet, sStatusWS As Worksheet
Dim rngInsitedata As Range, rngsStatusdata As Range, c As Range
Dim reportCurrentRow As Integer
Dim insitelastRow As Integer, trueOnAirlastRow As Integer, sStatuslastRow
As Integer
Application.Calculation = xlCalculationManual

Set sStatusWS = Worksheets("Starting Status 9-29-2009")
sStatuslastRow = sStatusWS.Range("A65536").End(xlUp).Row
Set rngsStatusdata = sStatusWS.Range("$C$2:$I$" & sStatuslastRow & "")

Set insiteWS = Worksheets("InSite Milestones")
insitelastRow = insiteWS.Range("A65536").End(xlUp).Row
Set rngInsitedata = insiteWS.Range("A2:A" & insitelastRow & "")

Set trueOnAirWS = Worksheets("True On Air Status")
trueOnAirlastRow = trueOnAirWS.Range("A65536").End(xlUp).Row
trueOnAirWS.Range("A2:I" & trueOnAirlastRow).ClearContents

reportCurrentRow = 2
For Each c In rngInsitedata.Cells
If c.Offset(0, 5) = "Actual" And c.Offset(0, 7).Value < "Actual" Then
trueOnAirWS.Range("A" & reportCurrentRow & "") = c.Value
trueOnAirWS.Range("B" & reportCurrentRow & "") = c.Offset(0,
1).Value
trueOnAirWS.Range("C" & reportCurrentRow & "") = c.Offset(0,
2).Value
trueOnAirWS.Range("D" & reportCurrentRow & "") = c.Offset(0,
3).Value
trueOnAirWS.Range("E" & reportCurrentRow & "") = c.Offset(0,
4).Value
trueOnAirWS.Range("F" & reportCurrentRow & "") = c.Offset(0,
5).Value
trueOnAirWS.Range("G" & reportCurrentRow & "") = c.Offset(0,
6).Value
trueOnAirWS.Range("H" & reportCurrentRow & "") = c.Offset(0,
7).Value

'If WorksheetFunction.CountIf(Column1, c.Offset(0, 2).Value) 0
Then
On Error Resume Next
trueOnAirWS.Range("J" & reportCurrentRow & "") =
WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False)
'End If
On Error GoTo 0
reportCurrentRow = reportCurrentRow + 1
End If
Next c

trueOnAirlastRow = trueOnAirWS.Range("A65536").End(xlUp).Row
For Each c In trueOnAirWS.Range("J2:J" & trueOnAirlastRow).Cells
If c.Value = "AFTER 9/30" Then
c.Offset(0, -1) = c.Value
End If
Next c
Application.Calculation = xlCalculationAutomatic

End Sub

"Patrick Molloy" wrote:

this issue is that VLOOKUP and other similar function raise an error when no
match is found. Wrap it in an error trap - personally, I do this within a
user defined function so that my error handling doesn't break, but not
everybody likes this ...

ON ERROR RESUME NEXT
result = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata,
7, False)
ON ERROR GOTO 0


"Ayo" wrote:

How do I go around this runtime error? I tried the following:

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < " " Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then
trueOnAirWS.Range("J" & reportCurrentRow & "") =
Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata,
7, False)
End If
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,058
Default Unable to get the VLookup property of the WorksheetFunction class
Probably related to the inputs you are supplying and how they are Dim'ed:

Sub servient()
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''
Dim c As Range, rngsStatusdata As Range
Set c = Range("A1")
c.Offset(o, 2).Value = 2
Set rngsStatusdata = Range("A1:Z100")
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''
If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < " " Then
MsgBox ("true")
Else
MsgBox ("false")
End If
End Sub

works just fine.
--
Gary''s Student - gsnu200905


"Ayo" wrote:

How do I go around this runtime error? I tried the following:

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < " " Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then
trueOnAirWS.Range("J" & reportCurrentRow & "") =
Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata,
7, False)
End If
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
Ayo Ayo is offline
external usenet poster
  
Posts: 489
Default Unable to get the VLookup property of the WorksheetFunction cl
this is my extire code:
Private Sub cmdUpdate_Click()
Dim insiteWS As Worksheet, trueOnAirWS As Worksheet, sStatusWS As Worksheet
Dim rngInsitedata As Range, rngsStatusdata As Range, c As Range
Dim reportCurrentRow As Integer
Dim insitelastRow As Integer, trueOnAirlastRow As Integer, sStatuslastRow
As Integer
Application.Calculation = xlCalculationManual

Set sStatusWS = Worksheets("Starting Status 9-29-2009")
sStatuslastRow = sStatusWS.Range("A65536").End(xlUp).Row
Set rngsStatusdata = sStatusWS.Range("$C$2:$I$" & sStatuslastRow & "")

Set insiteWS = Worksheets("InSite Milestones")
insitelastRow = insiteWS.Range("A65536").End(xlUp).Row
Set rngInsitedata = insiteWS.Range("A2:A" & insitelastRow & "")

Set trueOnAirWS = Worksheets("True On Air Status")
trueOnAirlastRow = trueOnAirWS.Range("A65536").End(xlUp).Row
trueOnAirWS.Range("A2:I" & trueOnAirlastRow).ClearContents

reportCurrentRow = 2
For Each c In rngInsitedata.Cells
If c.Offset(0, 5) = "Actual" And c.Offset(0, 7).Value < "Actual" Then
trueOnAirWS.Range("A" & reportCurrentRow & "") = c.Value
trueOnAirWS.Range("B" & reportCurrentRow & "") = c.Offset(0,
1).Value
trueOnAirWS.Range("C" & reportCurrentRow & "") = c.Offset(0,
2).Value
trueOnAirWS.Range("D" & reportCurrentRow & "") = c.Offset(0,
3).Value
trueOnAirWS.Range("E" & reportCurrentRow & "") = c.Offset(0,
4).Value
trueOnAirWS.Range("F" & reportCurrentRow & "") = c.Offset(0,
5).Value
trueOnAirWS.Range("G" & reportCurrentRow & "") = c.Offset(0,
6).Value
trueOnAirWS.Range("H" & reportCurrentRow & "") = c.Offset(0,
7).Value

If WorksheetFunction.CountIf(Column1, c.Offset(0, 2).Value) 0
Then
trueOnAirWS.Range("J" & reportCurrentRow & "") =
WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7, False)
End If
reportCurrentRow = reportCurrentRow + 1
End If
Next c

trueOnAirlastRow = trueOnAirWS.Range("A65536").End(xlUp).Row
For Each c In trueOnAirWS.Range("J2:J" & trueOnAirlastRow).Cells
If c.Value = "AFTER 9/30" Then
c.Offset(0, -1) = c.Value
End If
Next c
Application.Calculation = xlCalculationAutomatic
Exit Sub

End Sub
"Gary''s Student" wrote:

Probably related to the inputs you are supplying and how they are Dim'ed:

Sub servient()
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''
Dim c As Range, rngsStatusdata As Range
Set c = Range("A1")
c.Offset(o, 2).Value = 2
Set rngsStatusdata = Range("A1:Z100")
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''
If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < " " Then
MsgBox ("true")
Else
MsgBox ("false")
End If
End Sub

works just fine.
--
Gary''s Student - gsnu200905


"Ayo" wrote:

How do I go around this runtime error? I tried the following:

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < " " Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then
trueOnAirWS.Range("J" & reportCurrentRow & "") =
Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata,
7, False)
End If
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default Unable to get the VLookup property of the WorksheetFunction class
Another way..

If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0 Then
varData = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7,
0)
Else
'whatever
End If

If this post helps click Yes
---------------
Jacob Skaria


"Ayo" wrote:

How do I go around this runtime error? I tried the following:

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < " " Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then
trueOnAirWS.Range("J" & reportCurrentRow & "") =
Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata,
7, False)
End If
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
Ayo Ayo is offline
external usenet poster
  
Posts: 489
Default Unable to get the VLookup property of the WorksheetFunction cl
I get and "Object required" error when I used
If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0

"Jacob Skaria" wrote:

Another way..

If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0 Then
varData = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7,
0)
Else
'whatever
End If

If this post helps click Yes
---------------
Jacob Skaria


"Ayo" wrote:

How do I go around this runtime error? I tried the following:

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < " " Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then
trueOnAirWS.Range("J" & reportCurrentRow & "") =
Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata,
7, False)
End If
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default Unable to get the VLookup property of the WorksheetFunction cl
Column1 should be a range object referring to the 1st column of the range
rngsStatusdata.

If this post helps click Yes
---------------
Jacob Skaria


"Ayo" wrote:

I get and "Object required" error when I used
If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0

"Jacob Skaria" wrote:

Another way..

If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0 Then
varData = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7,
0)
Else
'whatever
End If

If this post helps click Yes
---------------
Jacob Skaria


"Ayo" wrote:

How do I go around this runtime error? I tried the following:

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < " " Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then
trueOnAirWS.Range("J" & reportCurrentRow & "") =
Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata,
7, False)
End If
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default Unable to get the VLookup property of the WorksheetFunction cl
Dim rngColumn1 as Range
Set rngColumn1 = sStatusWS.Range("C2:C" & sStatuslastRow)

If WorksheetFunction.CountIf(rngColumn1,c.Offset(0, 2).Value) 0 Then
varData = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7,
0)
Else
Msgbox "Not found"
End If


If this post helps click Yes
---------------
Jacob Skaria


"Ayo" wrote:

I get and "Object required" error when I used
If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0

"Jacob Skaria" wrote:

Another way..

If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0 Then
varData = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7,
0)
Else
'whatever
End If

If this post helps click Yes
---------------
Jacob Skaria


"Ayo" wrote:

How do I go around this runtime error? I tried the following:

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < " " Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then
trueOnAirWS.Range("J" & reportCurrentRow & "") =
Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata,
7, False)
End If
Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
Ayo Ayo is offline
external usenet poster
  
Posts: 489
Default Unable to get the VLookup property of the WorksheetFunction cl
Thanks Jacob. Works perfectly.

"Jacob Skaria" wrote:

Dim rngColumn1 as Range
Set rngColumn1 = sStatusWS.Range("C2:C" & sStatuslastRow)

If WorksheetFunction.CountIf(rngColumn1,c.Offset(0, 2).Value) 0 Then
varData = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7,
0)
Else
Msgbox "Not found"
End If


If this post helps click Yes
---------------
Jacob Skaria


"Ayo" wrote:

I get and "Object required" error when I used
If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0

"Jacob Skaria" wrote:

Another way..

If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) 0 Then
varData = WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata, 7,
0)
Else
'whatever
End If

If this post helps click Yes
---------------
Jacob Skaria


"Ayo" wrote:

How do I go around this runtime error? I tried the following:

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < " " Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then
trueOnAirWS.Range("J" & reportCurrentRow & "") =
Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata,
7, False)
End If
Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Unable to get the VLookup property of the WorksheetFunction class
Do you really want to do any comparisons to " "? Your =vlookup() could return a
single space character?

========

I'd use:

dim Res as variant 'could be an error

res = application.vlookup(location, sheets("City").range("a2:b3", 2, false)

with Worksheets("Master")
if iserror(res) then
.range("V3").value = "Missing"
else
.range("V3").value = res
end if
end with

==========
Saved from a previous post:

There is a difference in the way application.vlookup() and
worksheetfunction.vlookup() (or application.worksheetfunction.vlookup()) behave.

Application.vlookup returns an error that you can check:

dim Res as variant 'could return an error
res = application.vlookup(....)
if iserror(res) then
msgbox "no match"
else
msgbox res
end if

Application.worksheetfunction.vlookup raises a trappable error that you have to
catch:

dim res as variant
on error resume next
res = application.worksheetfunction.vlookup(...)
if err.number < 0 then
msgbox "no match"
else
msgbox res
end if
on error goto 0

(application.match() and application.worksheetfunction.match() behave the same
way.)

Personally, I find using the application.vlookup() syntax easier to read. But
it's personal preference.

Ayo wrote:

How do I go around this runtime error? I tried the following:

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < " " Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then

If Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value,
rngsStatusdata, 7, False) < "#N/A" Then
trueOnAirWS.Range("J" & reportCurrentRow & "") =
Application.WorksheetFunction.VLookup(c.Offset(0, 2).Value, rngsStatusdata,
7, False)
End If

--

Dave Peterson
Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Unable to get the VLookup property of the WorksheetFunction class
the best advice is, don't use worksheet functions in VBA.

I see you are using Offset, but why use vlookup at all then?

Use ONLY offset. It's actually more useful and easier to use than
vlookup anyway.
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid 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 get the VLookup property of the WorksheetFunction class Ayo Excel Discussion (Misc queries) 4 August 6th 08 10:00 PM
unable to get match property of WorksheetFunction class titus Excel Programming 3 September 6th 06 12:41 AM
Unable to get Match property of the WorksheetFunction class C++User Excel Programming 8 May 3rd 06 02:54 PM
Unable to get the Vlookup property of the WorksheetFunction class DoctorG Excel Programming 1 March 17th 06 06:49 PM
Unable to get the Vlookup Property of the WorkSheetFunction Class monagan Excel Programming 2 August 3rd 04 09:32 PM


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

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement