Searching multiple worksheets for a specific cell value

[jfoerch]

Hi...
I'm trying to employ some code (that I probably got from this site) that works just fine elsewhere in my spreadsheet but I can't seem to get to work in this slightly different application. Essentially I am looking at a column of cells and taking each value from that column and searching several other sheets in the workbook for that value. Then, I populate a new worksheet with the information that is associated with that cell value. Each cell value from that column can occur multiple times in the various sheets that I'm searching. Here is the code I'm trying to use.

Please Login or Register  to view this content.

I put comments in to kinda show what I'm trying to do and what error I'm getting. Basically I'm getting a run-time error 91, Object variable or With block variable not set. Not sure how to fix it so I would appreciate any help you can give... Suggestions regarding how to fix it or an alternative code that I can use.

Thanks.

[protonLeah]

Set Found = Cells.FindNext(After:=ActiveCell) ---> Set Found = Cells.FindNext(Found)

[jfoerch]

Thanks for the reply. I tried making the change and I'm getting the same result on the target worksheet and the same error.

Before when I run the macro and put a stop on the "If found.address....exit do" it populates the first line and there are no errors. When I hit play after that, that's when I get the run-time 91 error and it doesn't proceed to look for the next occurrence of the value. So, something indicated by that error ("Object variable or With block variable not set") is preventing the loop. Any thoughts?

[jfoerch]

What seems to be happening is that, before the set Found... line, when I mouse over it, "Found" matches "AtiveCell". But, then when I continue the macro, that line changes "Found" to Nothing. So, when I go to the next line, Found.address errors out because "Found" was set to Nothing on the previous line. Hope that makes sense...

[jfoerch]

I deleted the original text from this reply because it's irrelevant to the problem. The real problem (I think) as stated in the previous post is that "Found" gets set to Nothing and Nothing doesn't have an address. I can't figure out why that's happening (even though the value I'm searching for has other occurrences in the worksheet I'm searching) and it doesn't happen the other time I use this logic.

[jfoerch]

For reference, here is the code that is working fine. The only difference in the two is in this code, instead of populating cells on a worksheet, I'm populating textboxes on a userform. But, this identifies multiple occurrences of a value or string on the same page and searches all pages as expected.

Please Login or Register  to view this content.

This version doesn't make "Found" to be Nothing. I can't figure out what is different.

[protonLeah]

Please Login or Register  to view this content.

[jfoerch]

Thanks for the new code. I'm trying to use it and having a few problems...

1)The Option Explicit that you show at the top, when I put that in my code it puts that with the macro listed above... So it ends up with the CommandButton10 logic.

2) When I run the code, I'm getting a runtime error 1004, "Application defined or Object defined error, 1004". This error comes right after the Do loop starts on the line where I set the value of "descrip"

3) You are asking the sheet to search the sheets in the array. Those are actually the only sheets that I don't want to search. Can I exclude only those sheets, but search all others?

Thanks. Sorry this is becoming such a pain.

[jfoerch]

Playing around with it some more because I really could no figure out why it works elsewhere in my spreadsheet... Here's what I found...

I took that same logic and triggered it from a commandbutton on a userform instead of a CommandButton on a spreadsheet and it works fine.

Any idea why that would be?

[protonLeah]

1. I always put "Option Explicit" at the top of every module. It ensures that I Dim all my variables and don't misspell them later creating new variables accidentally.
2. I'm not sure why the error. Can you post a sample workbook with the sheet the button is on an one of the sheets to be searched?
3. Sorry, I overlooked that empty Else clause in your code so your original lines will work best to exclude those sheets. I don't know why it will work from a userform and not a sheet. A sample wb would help to debug what's going on.

[jfoerch]

Yeah, I was thinking that a sample spreadsheet would be a good idea. I did some more work with little luck. I am attaching a sample file (several of the worksheets that I'm bypassing in the logic are hidden sheets). I kept going with my original logic because I was most familiar with it and this is where I'm at...

1) The worksheet has 2 buttons. One opens a userform that has another button that does the sorting. With this option, the logic works with no errors, but still has a problem. It only searches for the first item in the list on the Storage tab. For some reason, it won't go to the next x value in the for/next loop. Probably because I really don't understand how the while/wend and do/loop logic works. Alternative code is welcome (like what you showed earlier) or fixing what I have is fine too.

2) The 2nd button tries to run the exact same logic from the spreadsheet command button rather than from a userform. In this case, I do get the same error that I mentioned before and I have no idea why. I guess my preference would be to not have to use the userform to launch the logic, but go direct from the spreadsheet command button. But, I can make either work.

Thanks for the help on this! I really appreciate it.

[protonLeah]

I made changes to cmd btns 1/3 similar to the previously posted.
As for as the line:

Please Login or Register  to view this content.

on btn 2 there was an occasion when the search value was found in cell B4 so the Offset(0, -7) fell off the left side of the sheet. So you get: "... Application defined or Object defined error, 1004 ... "

[jfoerch]

Excellent. Thanks. Won't be able to get to it yet tonight, but I'll try to integrate into my spreadsheet tomorrow or early next week. Thanks again!

[jfoerch]

Worked great! Thanks.