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

 

[JackDanIce]

Untested, however, try:

Private Sub CommandButton1_Click()

    Dim fPath   As String
    Dim rng     As Range
    Dim rngPwd  As Range
    Dim x       As Long
    
    Application.ScreenUpdating = False
    
    fPath = Sheets("Admin").Range("I3").value
    
    With Sheets("PWDs")
        x = .Cells(.rows.count, 5).End(xlUp).row
        Set rng = .Range("E2:E" & x)
    End With
    
    Set rngPwd = rng.find(what:=fPath, LookIn:=xlValues, lookat:=xlWhole).Offset(, -2)
    
    If rngPwd Is Nothing Then
        MsgBox "Cannot find: " & fPath & " in sheets PWDs, please check and try again", vbExclamation, "Path not found"
        Set rng = Nothing
        Exit Sub
    End With
            
    Workbooks.Open filename:=fPath, Password:=PWD
            
    Set rng = Nothing
    Set rngPwd = Nothing
            
End Sub

 

[JanetW]

Hi

Thank you for your reply - I appear to have posted this twice somehow!

I have now resolved this using a bit of the above code combined with another reply - thanks for your help x

 

[JackDanIce]

You're welcome Janet, glad it's resolved