Run-time error '91' - Don't understand why I have this error

[ea_kenny]

Hello,

My name is Kenny, I came across this forum while googling fro answers. I have tried numerous google hits but can't figure out what I've done wrong.
I have some background of coding while I was at school, but my line of work is now in an other subject then programming. So with the basis I have and google I have tried to create a worksheet for asset management.

Maybe first of all a little sketch of what I like to do. At work we have a lot of material and users are constantly borrowing these things. As the previous system got cluttered and was getting inefficient we thought of a new excel file. We like to work with a hand scanner and barcodes to fill in mutation lines. Also we will use this scanner to enter all new material in to the excel. So first steps would be this:

- An administrator scans some of the data to enter the new material (worksheet NEW)
- When all data is filled in, it will be transferred to a new worksheet called OVERVIEW
- When somebody wants to borrow something he just takes his material and scans all needed barcodes (user, object, location,...)
- The last cell will be filled in automatically with the date.
- When the last cell is entered the data will be automatically updated on the worksheet called OVERVIEW
- After this is done, the active cell is set to the first cell on the next row for a next mutation

Now that coding is completed and I started testing I received an error: "Run-time error '91': Object variable or With block variable not set". The line with code "barcodecell = rngX.Address" is highlighted.

I figured out while searching the internet that it has something to do with the find and an object I want to set. But I can't figure out what I have done wrong in the Find code. Can someone have look?

Please Login or Register  to view this content.

If you have the time and could spare the effort, could you also go through the rest of the code to check this? It would be much appreciated. Because I guess I have made other errors as well, because I'm still a newby to this.

Kind regards and thanx in advance!

Beheer_frm.xlsm

[ea_kenny]

I've also added the excel workbook to make it a bit easier..

[Andy Pope]

It fails when the Find method can to actually find what it is looking for.

you need to check the returned reference.

Please Login or Register  to view this content.

You may also need code to handle things when the find does fail.

[ea_kenny]

Hey Thanks for your reply.
I have altered the code with the following but still get this "91" error.

Changed part:

Please Login or Register  to view this content.

The strange thing with this was that when I had the part to update the worksheet OVERVIEW behind the part of making the next line active (see comments in code) the code worked and returned (in a messagebox that I used for test purpose) the cell $A$3. But then he allready went to the next line. So I placed it in front of the last part of the code and from then on I received the error.

[Andy Pope]

Your code is the exact opposite of my suggestion.

Please Login or Register  to view this content.

[ea_kenny]

OK thnx! That handles the error handling very well. I also added GoTo 1 and added 1 before End sub so it leaves the code after showing the msgbox.
But now I have tried with the correct data and it doesn't find the barcode. Although the variable barcode has the data that it should have.

While waiting for a reply I also tried MsgBox rngX.value (in my solution, so opposite to what you suggested) and then I received the error on that line...

[ea_kenny]

So now the code looks like this, but it never finds the correct barcode. It always goes to the messagebox.

Please Login or Register  to view this content.

[Andy Pope]

If the user enters information and move down rather than across your code will do different things.

update the message box code

Please Login or Register  to view this content.

Target and activecell are not the same thing.

[ea_kenny]

Information is entered with a hand-scanner (one you see in shops) after entering the value it automatically tabs. So there is no other direction the user can move in.
The "MsgBox barcode" statement I only used as a verification to check what was in the variable barcode. I accidentally left it in. After altering it like you suggested a message box appeared with the data in barcode and twice a 3 for ActiveCell.Row and Target.Row.

[Andy Pope]

If the barcode is found then for me the code progress to

Please Login or Register  to view this content.

At which point it will fail as OVERVIEW is not the active sheet so you can not select the barcodecell.

[ea_kenny]

Indeed, I also noticed why the find failed. The filled in barcode in OVERVIEW had code behind it =SCANDATA_NIEUW!B2.
So it probably was comparing that instead of the value...
And indeed then it turns to the part to activate the cell and like you said it doesn't work.
So I'll have to make it active first.

[ea_kenny]

OK I see I still have some bugs left... because after I changed code to this

Please Login or Register  to view this content.

it goes to

Please Login or Register  to view this content.

where it says its missing an object...??????

[ea_kenny]

OK all I had to do was to remove the Set in front!
Thnx for your support Andy Pope!