Runtime error '1004':

[Guest]

I get the error, "Unable to get VLookup property of the WorksheetFunction
class" for the following sub:

Sub ShowEmpolymentFollowup()
PasswordOff
HideAllSheets
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim StudentCount As Integer
Set sh1 = Sheets("Employment Followup")
Set sh2 = Sheets("Student Info")
StudentCount = 3
Sheets("Employment Followup").Visible = True
Sheets("Student Info").Visible = True
For StudentNum = 33 To 550
If Application.WorksheetFunction.VLookup(sh2.Range("B" & StudentNum),
sh2.Range("A33:J550"), 10, False) = 1 Then
sh1.Range("A" & StudentCount).Value = sh2.Range("B" &
StudentNum).Value
StudentCount = (StudentCount + 1)
End If
Next StudentNum
Sheets("Student Info").Visible = False
Sheets("Employment Followup").Select
PasswordOn
End Sub

Please help me fix this. Thank you in advance.

 

[Dave Peterson]

application.worksheetfunction.vlookup() will cause a runtime error if there is
no match.

I'd use something like:

dim Res as variant
....

For StudentNum = 33 To 550
res = application.vlookup(sh2.Range("B" & StudentNum), _
sh2.Range("A33:J550"), 10, False)
if iserror(res) then
'what should happen if there is an error
elseif res = 1 then
sh1.Range("A" & StudentCount).Value = sh2.Range("B" & StudentNum).Value
end if
StudentCount = (StudentCount + 1)
Next StudentNum