VBA run time error 91 - Object varialble or With block variable not set

[JanetW]

Hi

I am a relatively new VBA user and am having some trouble with the following code.

I am getting runtime error 91 and the cursor goes to ** when i click ok

What i am basically trying to do is:
I have a file which stores passwords used to save files by team members for tests (for privacy from other team members)
The manager wants to be able to acces these files for review by accessing the relevant pwd for the file

Private Sub CommandButton1_Click()
Dim Filepath As String
Dim PWDSearch As Range
Dim SearchRange As Range
Dim PWD As String


Filepath = Worksheets("Admin").Range("I3")
Set SearchRange = Worksheets("PWDs").Range("E2", Worksheets("PWDs").Range("E1").End(xlDown))
** Set PWDSearch = SearchRange.Find(what:=Filepath, MatchCase:=True, lookat:=xlWhole).Select
PWD = ActiveCell.Offset(0, -2)

If PWDSearch Is Nothing Then
MsgBox "File does not exist"
Else
Workbooks.Open (Filepath), Password:=PWD
End If


End Sub

If anybody can shed some light on what I am doing wrong I would be really grateful!!

Thanks

Janet

 

[theBardd]

Try dropping the .Select at the end.

 

[Norie]

Try this.

Private Sub CommandButton1_Click()
Dim PWDSearch As Range
Dim SearchRange As Range
Dim Filepath As String
Dim PWD As String

    Filepath = Worksheets("Admin").Range("I3")
    Set SearchRange = Worksheets("PWDs").Range("E2", Worksheets("PWDs").Range("E" & Rows.Count).End(xlUp))
    Set PWDSearch = SearchRange.Find(what:=Filepath, MatchCase:=True, lookat:=xlWhole)

    If PWDSearch Is Nothing Then
        MsgBox "File does not exist"
    Else
        PWD = PWDSearch.Offset(0, -2).Value

        Workbooks.Open (Filepath), Password:=PWD
    End If

End Sub

 

[JanetW]

Hi

Thanks for your response -I have tried this and it is not locating the filename in the search even though it is in cell e2 and therefore returns the message box saying file does not exist


???

Janet

 

[Norie]

Janet

Do you mean the value in I3 on Admin matched the value in E2 on the PWDs?

 

[JanetW]

Hi

Yes, however, is was a concatenation so i have added LookIn:=xlValues

to the PWDSearch line and it now works - thank you for your help