Error - Object variable not set (Error 91)

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

I have worksheet, like this:

http://radikal.ru/F/s15.radikal.ru/i...03dc3.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/i...e331f.jpg.html

http://radikal.ru/F/i061.radikal.ru/...3a719.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.

Hello Przemyslav,

Welcome to the Forum!

This is a very common problem for beginners using the Find method in VBA. You need to use a range object variable to hold the result of the find operation. If successful then the object points to the cell where the match was found. If no match is made then the object variable is set to the special object value of Nothing. The object variable needs to be checked to decide what action needs to be taken.

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim FoundIt As Range

    If Target.Count = 1 Then
      If Target = Range("C1") Then
         Set FoundIt = Columns("AK").Find(what:=Target.Value, Lookat:=xlWhole)
         If FoundIt Is Nothing Then MsgBox Target & " Not Found.": Exit Sub
         Application.EnableEvents = False: FoundIt.Select: Application.EnableEvents = True
      End If
    End If

End Sub

Hello Przemyslav,

If this solution solved your problem, please mark the thread as solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save