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

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.

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!

Try changeinging the .Find argument.
LookIn:=xlFormulas

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

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

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

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.

Originally Posted by Ivkosky

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.

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...

Originally Posted by Ivkosky

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.

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