Unable to get the VLookup property of the WorksheetFunction class

  • Thread starter Thread starter Ayo
  • Start date Start date
A

Ayo

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
 
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)
 
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
 
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.
 
I get and "Object required" error when I used
If WorksheetFunction.CountIf(Column1,c.Offset(0, 2).Value) > 0
 
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
 
Column1 should be a range object referring to the 1st column of the range
rngsStatusdata.

If this post helps click Yes
 
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
 
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
 
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.
 
Thanks Jacob. Works perfectly.

Jacob Skaria said:
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
Click to expand...
 
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.
 
Back
Top