VBA VLookup Problem: Run-Time error '1004'

[Guest]

Pasted below is code that when ran is resulting in a "Run-Time error '1004'
Unable to get the VLookup property of the WorksheetFunction Class." The
problem is in the string of code where I am trying to apply a password to a
file based on a result from a vlookup. Can someone help me get this
corrected, I believe I am very close...thanks.


Sub Protect_File_Labor()

Dim sCurrFName As String
Dim X As Integer
Dim Y As Range
Set Y = Range("d17")


With Application
.ScreenUpdating = False
.DisplayAlerts = False
sCurrFName = Dir(Y.Value & "*.xls")
Do While sCurrFName <> ""
Workbooks.Open (Y.Value & sCurrFName)
Workbooks(sCurrFName).SaveAs (Y.Value & sCurrFName),
Password:=WorksheetFunction.VLookup(sCurrFName, Range("k1:l3"), 2, 0)
Workbooks(sCurrFName).Close
sCurrFName = Dir
Loop
.ScreenUpdating = True
.DisplayAlerts = True
End With

End Sub

 

[Guest]

HURLBUT
Workbooks(sCurrFName).SaveAs (Y.Value & sCurrFName),
Password:=application.WorksheetFunction.VLookup(sCurrFName, Range("k1:l3"),
2, 0)

 

[Guest]

I appreciate the effort, but that didn't work...any other suggestions?

 

[Guest]

hmm maybe try to put that worksheetfuntion or the formula equivalent into a
cell, pull the value from that cell and then delete the formula?

 

[Dave Peterson]

That means that you're not finding a match.

You can either check for the run time error:

dim pwd as string
on error resume next
pwd = WorksheetFunction.VLookup(sCurrFName, Range("k1:l3"), 2, 0)
if err.number <> 0 then
'not found
else
'found it
end if

or you can drop the worksheetfunction and use application--and check for an
error:

dim pwd as variant
pwd = application.VLookup(sCurrFName, Range("k1:l3"), 2, 0)
if iserror(pwd) then
'not found
else
'found
end if

I think the application.vlookup() is easier to read later.