inputbox get run-time error 91

[reddcannon]

I have this button with the code below it works 1 or 2 times, then I get the Run-time error 91, object variable or with block variable not set.

It will not work at all if I protect my worksheet with a password, which I must do. This spreadsheet is just for people to enter a part number and look up prices. That is it. I just need them to click the button and type the part number and it finds it.


Private Sub CommandButton2_Click()

'Opens box and ask what do they want to search
searchthis = InputBox("Type in a location keyword.", "Property Search")
'Tells where to search
Columns("a").Select
'and then search in them whatever the user entered:
Selection.Find(What:=searchthis, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext).Activate
End Sub

 

[VoG]

Try

Private Sub CommandButton2_Click()
Dim searchthis As String, Found As Range
searchthis = InputBox("Type in a location keyword.", "Property Search")
Set Found = Columns("A").Find(What:=searchthis, LookIn:=xlFormulas, LookAt:=xlPart)
If Not Found Is Nothing Then Found.Select
End Sub

 

[reddcannon]

Peter, thanks a million, but I should have said, I need it to look in Column A and C I tried changing it to a:c that did not work.

 

[VoG]

Try

Private Sub CommandButton2_Click()
Dim searchthis As String, Found As Range
searchthis = InputBox("Type in a location keyword.", "Property Search")
Set Found = Range("A:A,C:C").Find(What:=searchthis, LookIn:=xlFormulas, LookAt:=xlPart)
If Not Found Is Nothing Then Found.Select
End Sub

 

[reddcannon]

OK Peter, it works, but as soon as I save it with the protect worksheet password and reopen, it does not lookup

 

[VoG]

Try like this, change xxx to suit

Private Sub CommandButton2_Click()
Dim searchthis As String, Found As Range
Me.Unprotect Password:="xxx"
searchthis = InputBox("Type in a location keyword.", "Property Search")
Set Found = Range("A:A,C:C").Find(What:=searchthis, LookIn:=xlFormulas, LookAt:=xlPart)
If Not Found Is Nothing Then Found.Select
Me.Protect Password:="xxx"
End Sub

 

[reddcannon]

FREAKING GENIUS, Man o Man, you have saved my day. Do you all do this for free, can a donation be sent or what. I am totally new to these forums.

 

[VoG]

All for free (fortunately for you, not for me ).

Send me a virtual G&T, thanks