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

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

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

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

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

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

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

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

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

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

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

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.