Error 91, Object Variable or With Block variable not set.

This one has me baffled. I have a workbook containing two sheets, Inventory and PO. On the PO sheet, I have a column that has Validation turned on: Show List and Source is set to a column on the Inventory sheet. So now I can click on any of the cells in that column, and select an item from the dropdown list (the list is provided by the Inventory sheet.)

So far so good. It works as expected (I pick the item, and my macro will automatically fill the rest of the information in the rest of that row.) Except for one problem: if I happen to manually type something in, the macro fails with "Error 91, Object Variable or With Block variable not set." I've gone over Microsoft's help and Excel's help and I simply can't figure out where or why it fails. The specific code that runs and does the search on the Inventory sheet, which is where the error occurs, is as follows:

Please Login or Register  to view this content.

Like I said, if I pick an item from the dropdown list, it works just fine. But if I happen to (accidentally) type something in, it throws an error.

Why's that?

And is it possible that when (or if) I do type something in, that it automatically fills in the dropdown list with possible matches? Kinda like how a web browser will do text-completion when you start typing in a URL?

What is this code for?

Originally Posted by AMK4

CODE]
SearchFor = Target.Value
With myInventorySht.Range("B:B")
TheRow = .Cells.Find(What:=SearchFor, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
End With

It looks like you are trying to find the row of the cell that changed? If so, then just use TheRow=target.row

I would make that change first, then see if it still happens.

Yes and no. I'm trying to find the row, but on a different sheet. Sheet(1) contains an inventory list. Sheet(2) contains a column in which each cell has validation turned on, with the source being a column on Sheet(1). So, when I select an item in any of those cells [on Sheet(2)], I want it to tell me which row [on Sheet(1)] that item falls on. That's why I have to search for it, and why I can't simply use Target.Row.

Like I mentioned before, if I pick an item from the drop down list, everything works fine. But, if I happen to manually type something in, that's when the errors happens. Maybe it has to do with the validation process? I don't know.