VBA VLookup Problem: Run-Time error '1004'

[hurlbut777]

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

[ben]

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


"hurlbut777" wrote:

> 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
>

[hurlbut777]

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

"ben" wrote:

> HURLBUT
> Workbooks(sCurrFName).SaveAs (Y.Value & sCurrFName),
> Password:=application.WorksheetFunction.VLookup(sCurrFName, Range("k1:l3"),
> 2, 0)
>
>
> "hurlbut777" wrote:
>
> > 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
> >

[ben]

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?

"hurlbut777" wrote:

> I appreciate the effort, but that didn't work...any other suggestions?
>
> "ben" wrote:
>
> > HURLBUT
> > Workbooks(sCurrFName).SaveAs (Y.Value & sCurrFName),
> > Password:=application.WorksheetFunction.VLookup(sCurrFName, Range("k1:l3"),
> > 2, 0)
> >
> >
> > "hurlbut777" wrote:
> >
> > > 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
> > >

[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.

hurlbut777 wrote:
>
> 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

--

Dave Peterson