locked
Error 91 received for macro using InputBox and Find RRS feed

  • Question

  • I received the following error:

    Run time error '91':
    Object variable or With block variable not set

    For the following macro -

    Sub FineMe()
    '
    Dim myValue As String
    '
    myValue = InputBox("Please enter your search criteria")
    '
        ActiveSheet.Cells.Find(What:=myValue, _
        After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    End Sub

    When I reviewed the macro the myValue did indicate that it held the input value.

    Why am I getting this error?
    Thank you

    Wednesday, January 19, 2011 12:45 AM

Answers

  • The error will occur if the entry in the InputBox is not in the Worksheet after the Active Cell.

    You use either

    Dim myValue As String
    '
    On Error Resume Next
    myValue = InputBox("Please enter your search criteria")
    '
       ActiveSheet.Cells.Find(What:=myValue, _
       After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, _
       SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
       False, SearchFormat:=False).Activate

    to prevent the error from occurring or some more elaborate error trapping that would advise the user that the contents of the InputBox were not found.


    Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "PMcGuire_Connect" wrote in message news:4e845a5e-a696-4195-b6d0-5ab93d3aa244@communitybridge.codeplex.com...

    I received the following error:

    Run time error '91':
    Object variable or With block variable not set

    For the following macro -

    Sub FineMe()
    '
    Dim myValue As String
    '
    myValue = InputBox("Please enter your search criteria")
    '
        ActiveSheet.Cells.Find(What:=myValue, _
        After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    End Sub

    When I reviewed the macro the myValue did indicate that it held the input value.

    Why am I getting this error?
    Thank you


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by Bruce Song Wednesday, February 2, 2011 10:32 AM
    Wednesday, January 19, 2011 1:02 AM

All replies

  • The error will occur if the entry in the InputBox is not in the Worksheet after the Active Cell.

    You use either

    Dim myValue As String
    '
    On Error Resume Next
    myValue = InputBox("Please enter your search criteria")
    '
       ActiveSheet.Cells.Find(What:=myValue, _
       After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, _
       SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
       False, SearchFormat:=False).Activate

    to prevent the error from occurring or some more elaborate error trapping that would advise the user that the contents of the InputBox were not found.


    Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "PMcGuire_Connect" wrote in message news:4e845a5e-a696-4195-b6d0-5ab93d3aa244@communitybridge.codeplex.com...

    I received the following error:

    Run time error '91':
    Object variable or With block variable not set

    For the following macro -

    Sub FineMe()
    '
    Dim myValue As String
    '
    myValue = InputBox("Please enter your search criteria")
    '
        ActiveSheet.Cells.Find(What:=myValue, _
        After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    End Sub

    When I reviewed the macro the myValue did indicate that it held the input value.

    Why am I getting this error?
    Thank you


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by Bruce Song Wednesday, February 2, 2011 10:32 AM
    Wednesday, January 19, 2011 1:02 AM
  • Much appreciated.

    Thank you.

    Wednesday, January 19, 2011 1:53 AM