Error - Object variable not set (Error 91)

[Przemyslav]

Hi everybody Nice to meet you. I need some help with VBA.

I have worksheet, like this:

http://radikal.ru/F/s15.radikal.ru/i189/1010/13/515dfb403dc3.jpg.html

I have day-list. When i choose a day from this list, Excel should take me to another cell. This cell is ilustrated on third picture - column AK.

http://radikal.ru/F/s59.radikal.ru/i165/1010/44/b4327d7e331f.jpg.html

http://radikal.ru/F/i061.radikal.ru/1010/e6/cfa83e23a719.jpg.html

But when I choose day on list I have this error:

Object variable not set (Error 91)

When I click Debug button Escel shows me, that this line is wrong in VBA:

Columns("AK").Find(what:=Target.Value, Lookat:=xlWhole).Select

This is my all code for this function:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Target = Range("C1") Then
  Columns("AK").Find(what:=Target.Value, Lookat:=xlWhole).Select
End If
End If

End Sub

What I do wrong ??

P.S. My sheets have to be protect in Excel.

Thanks for any help.

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Found As Range
If Target.Count = 1 Then
    If Target.Address(False, False) = "C1" Then
        Set Found = Columns("AK").Find(what:=Target.Value, Lookat:=xlWhole)
        If Not Found Is Nothing Then Found.Select
    End If
End If
End Sub

 

[Przemyslav]

VoG your code is OK, because I don't have any error.

But:
1. When I have protected sheet and I choose something from the list, nothing happen
2. When I have unprotected sheet, it's working.

Cell C1 in properties isn't blocked and hidden.

I have to protect sheet, because some of cells should be not able to edit.

Thanks for help

 

[VoG]

Try this - change abc to the actual password

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Found As Range
If Target.Count = 1 Then
    If Target.Address(False, False) = "C1" Then
        Me.Unprotect Password:="abc"
        Set Found = Columns("AK").Find(what:=Target.Value, Lookat:=xlWhole)
        If Not Found Is Nothing Then Found.Select
        Me.Protect Password:="abc"
    End If
End If
End Sub

 

[Przemyslav]

You are the best !!! It's working for me now Thanks for your help

I have learnt something new

Problem solved.