For Each with Find and WildCard

Please Login or Register  to view this content.

I am using this code to check if data in a list is found in another list.

i now need to reverse this and have it check the other way around.

My problem is that when I simply change sheet names and ranges everything comes up as not found.
I think this is due to the data on ws1 containing extra text/numbers than what is being searched for.
In this function we take care of that with the LookAt argument.

What I was thinking is finding a way to put in the For Each argument a wildcard. So yes I am looking for v (value) but I need to find a specific part of that (a 5 digit number) on the other sheet (because it only contains the numbers.)

It there a simple way to alter this code slightly to obtain the results I am looking for, and if not then how should I change this?

Please Login or Register  to view this content.

Hi cwhite86.

The .find syntax can be very frustrating at times.

Your code follows exactly as you wrote it:

Please Login or Register  to view this content.

I have not run your code, since you did not provide a sample workbook.

My speculation is that your problem is with the myRange object. When declaring the object, you probably also need to specify a Sheet.

For example:

Please Login or Register  to view this content.

Additionally, the 'With' syntax is very useful if used properly. In many cases it gives a false sense of security, mostly because of a missing DOT '.' Even though the syntax is very efficient, I try to avoid it because it causes many errors that are very difficult to find and debug.

I hope this helps.

Lewis

Sorry for not posting a book. Here is one.

This is a sheet that will be used in an office setting to validate physical inventory (mapped out on the All Warehouses Tab)
against what our computer Inventory says we should have (dubbed INV Master on this sheet.)
The INV Master sheet is populated by a copy and paste off the system.


I have used buttons in this case to help those who aren't as familiar with excel to navigate through sheets and then work through the currently 4 step process.


All buttons work as intended. You will see that as you work through the steps the the INV master sheet will highlight yellow products that are found
and leave in red by default those that are not.

This is has been a ongoing process and this forum has been a big help throughout the building phases.

Step 5 will be to highlight the sheets BI2C and BW2C as each number is found on the INV Master Sheet
This is the opposite of what is happening in MODULE 2. My goal was to take that module and reverse it to perform the needed task.


My post here was just trying to get the idea of how to do it with one sheet and one column, then go from there. But the whole project would include some 18 sheets similar to BI2C and BW2c.


I have been receiving help, then breaking down the provided code so that I can understand it enough to fit it for our need.
I am relatively new to VBA, so its a pretty big learning curve here.


Thanks

That highlighted Row 4, and the Title in Row 1.


I have not used the trim function yet, so I was trying to look through and see if I could figure out why but honestly I am at a loss.


The goal is to check the numbers under each catergory (Range A5:O100 would cover any possible scenario, preferably would ignore rows 1 - 4)



Not to be a bother, but to help me really learn here could you break down that code so I can see the logic?
I understand the basic body of the code, though there are several things I haven't used yet.

I see how the LightUp function is set apart, and how you used Exit Sub to do so.
LightUp contains the Find function and of course the IF command and the color command.

The main SUB body above that is establishing the ranges and the trim argument though I don't fully understand what each line is establishing.




Thanks so much!

Ok, apologies


that worked fine in the desensitized test book I gave you, but not in the normal one.

So obviously I missed something in converting it over, I will look into that.


Thanks

Here is a book with the changed lines in the INV Master sheet

The spacing will vary in each line from product to product.
This isn't a fixed list, this list will change everyday, as does the BI2C and BW2C pages.

Works beautifully!

I didn't even bother testing it on desensitized book, it works in the actual Book!


So what was the significance of adding er and setting that as the limit in r?


And no problem on the rep, anyone who takes this much time to help a newbie who is in way over their head deserves more than that.

That solves that for now.

I am sure I will post when I get to the next step,

which will be creating a list of all the cells that are not blank and match a given color.

To google i go, I'll start a new thread if/when I get stumped.