Finding the correct value

[CobraLAD]

I have all my items on sheet1 with a history to the right of each item. I am using advanced filter, to filter the item on sheet5. I want to see where my short items was before the are marked short, that means the second last entry on the history. This code gives an error "Object variable or With block variable not set"

Please Login or Register  to view this content.

I want to find the second last entry of the history on sheet1 and display it on sheet5, 7 columns away from each item

[Andrew-R]

You're running both a .End and a .Offset on the results of a .Find - so if the value you're looking for isn't found then the .Find will return a range of Nothing, which will generate a 1004 error when you try to .End or .Offset from it.

You should use a Set to assign the result of the .Find to a range type variable and then check that variable is not Nothing before attempting to do anything with the range returned.

[CobraLAD]

Thanks Andrew, understand what you are saying, but not sure how to set it up.

I got it to work.

Please Login or Register  to view this content.

[CobraLAD]

Thanks Andrew, understand what you are saying, but not sure how to set it up.

I got it to work.

Please Login or Register  to view this content.

[OllieB]

CobraLAD,

You almost had it

change

Please Login or Register  to view this content.

into

Please Login or Register  to view this content.