Cells.Find in For Loop Failing When Search Term Not Found

I'm hoping someone can help me out with this one.

Please Login or Register  to view this content.

This keeps failing on me with "RTE '91': Object variable or With block variable not set. It happens as soon as the second cells.find (MerchandiseColumn) doesn't find what it's looking for on the very first sheet. For the life of me I haven't been able to figure out what the deal is and why my On Error isn't working. Any help would be seriously appreciated.

Justin

Try putting "Resume next" after your Errorhandler 2:

Also, I'm from Branford, CT- howdy, neighbor!

Please Login or Register  to view this content.

Also, your error may be coming from the way you declare

Please Login or Register  to view this content.

Please Login or Register  to view this content.

but without the workbook it's difficult to tell.

I will definitely give this a go. Thank you!

Originally Posted by carlmon

Also, your error may be coming from the way you declare

Please Login or Register  to view this content.

Please Login or Register  to view this content.

but without the workbook it's difficult to tell.

Good eye! This was my fault from when I transferred the block to my post. I checked my code and the space isn't in there. Thanks for looking out!

Originally Posted by hrlngrv

I'd be tempted to rewrite this as

Please Login or Register  to view this content.

I'm fastidious about error trapping. Also, this restricts number formatting to the used range in each worksheet. Maybe you want to extend this for many more rows, but I suspect you don't mean to extend it all the way down to row 1,048,576.

Beyond that, if ENCUMBRANCE or MERCHANDISE are found in the (k-1)th worksheet but not the kth worksheet, the EncumbranceC.. and MerchandiseC.. variables would retain the values from the (k-1)th worksheet rather than be set to 0 or Nothing. You have to reset those variables just before Next in each For loop.

Maybe not a potential problem, but avoid one anyway: if sht is type Worksheet, iterate over RollWorkbook.Worksheets rather than RollWorkbook.Sheets.

Finally, use sht.Cells rather than just Cells. You're not activating different worksheets as you iterate through RollWorkbook.Worksheets, so unadorned Cells refers to ActiveSheet.Cells in each iteration.

I tried using this because everything you're saying made so much sense but when I ran it, it didn't format Anything. I haven't had a chance to do any troubleshooting but when I do, I'll let you know what I find.

Thanks everyone for your help! I decided to just start from scratch and it worked! Here is what I ended up going with:

Please Login or Register  to view this content.

Sorry. I should have spotted this before.

I modified your original code, but I failed to spot that you hadn't initialized RollWorkbook. Without doing so when error trapping is active, the macro does nothing useful. If you initialize it using a Set statement, the rest of the macro works correctly.