+ Reply to Thread
Results 1 to 12 of 12

FIND function returns Runtime Error 91 in VBA

  1. #1
    Registered User
    Join Date
    04-16-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    23

    Question FIND function returns Runtime Error 91 in VBA

    Hi there


    I would be grateful if you could help me with the following issue - I have two large datasets in two sheets and I need to take some data from both and compile a third datasheet in another sheet. I am trying to avoid If clauses and loops because I would wait forever till the computation is done. I therefore wanted to use the FIND function to find out what is the appropriate row in another dataset and then use some values from this row. The code is the following:

    x=2

    Sheets("Dataset2").Activate
    ActiveSheet.Range("AR:AR").Select
    ActiveSheet.Range("AR:AR").Activate

    Cells.Find(What:=(Sheets("Dataset1").Cells(x, 1).Value), After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

    u = ActiveCell.Row

    The result should be the value of "u", which will be then used as a row reference, i.e. Cells(u,1)... But I am getting Error 91 - Object variable not set. However, I don't know where it refers to, because the only object in the FIND function is the Cell., which I don't have to define. Or do I?

    I would very much appreciate any help here.

    Thanks!
    Ivan

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8,102

    Re: FIND function returns Runtime Error 91 in VBA

    It could also mean that it didn't find a match and cannot .Activate the cell.

    Try something like this...

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-16-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: FIND function returns Runtime Error 91 in VBA

    Thanks a lot for the swift reply!

    I have tested your code and it returned the message box as if the cell with the given value was not there. However, I have tested it before, using a normal LOOKUP function and it matched those two cells perfectly! The cells are the date-time combined cells and therefore I tested my code with other type of data (generic number) - again, it worked! The problem is therefore with the date-time combined cells... Very annoying... Could you please help me to overcome this bug?

    Thanks a lot!

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8,102

    Re: FIND function returns Runtime Error 91 in VBA

    Try changeinging the .Find argument.
    LookIn:=xlFormulas

  5. #5
    Registered User
    Join Date
    04-16-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: FIND function returns Runtime Error 91 in VBA

    Thanks for the advice, but the same result... Any other ideas would be very much appreciated!

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8,102

    Re: FIND function returns Runtime Error 91 in VBA

    As a test, put this formula in a cell. If it returns TRUE, they match. If it returns FALSE then do not match.

    =DataSet2!AR? = DataSet1!A2

    Change the ? to the AR row it is suppose to match

  7. #7
    Registered User
    Join Date
    04-16-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: FIND function returns Runtime Error 91 in VBA

    Thanks AlphaFrog, it returned TRUE... What should be my next steps then? Thanks a lot for your kind help!

  8. #8
    Registered User
    Join Date
    04-16-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: FIND function returns Runtime Error 91 in VBA

    One more thing, which crossed my mind now: the column AR in the Dataset2 contains some empty cells... Could it be the case that the FIND function stops when it runs to an empty cell? I doubt so, but I am getting desperate and therefore trying anything possible.

  9. #9
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8,102

    Re: FIND function returns Runtime Error 91 in VBA

    Quote Originally Posted by Ivkosky View Post
    One more thing, which crossed my mind now: the column AR in the Dataset2 contains some empty cells... Could it be the case that the FIND function stops when it runs to an empty cell? I doubt so, but I am getting desperate and therefore trying anything possible.
    Empty cells are not a problem.

    Try this instead.

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    04-16-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: FIND function returns Runtime Error 91 in VBA

    Thanks a lot for this! It returned a message box "Match found in row 14899"! I assume that if I disregard the message box bid, I can use the Match function, as you wrote it, for the whole code. Just one more question - it can also happen that the value won't be there... In that case, the code shall skip it and move to another x in the Dataset1. Could you please advise how to code it? Sorry for this, but I don't want to play around with your code anymore, since it is working now...

  11. #11
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    8,102

    Re: FIND function returns Runtime Error 91 in VBA

    Quote Originally Posted by Ivkosky View Post
    Thanks a lot for this! It returned a message box "Match found in row 14899"! I assume that if I disregard the message box bid, I can use the Match function, as you wrote it, for the whole code. Just one more question - it can also happen that the value won't be there... In that case, the code shall skip it and move to another x in the Dataset1. Could you please advise how to code it? Sorry for this, but I don't want to play around with your code anymore, since it is working now...
    Remove the message boxes but keep the If-Else-End If . The code you put in between will run based on if a match was found or not.

  12. #12
    Registered User
    Join Date
    04-16-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: FIND function returns Runtime Error 91 in VBA

    Thanks a lot AlphaFrog - I appreciate your help very much! I will mark this thread as solved. Thanks again...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1