"Find" method when two columns in the same row

[LCS]

Hey all,

I have a program where the user selects a number in a cell based on a criteria. This number can be under a group (Baseball), and a sub group (Yankees). If a number, 2 (restricted by data validation), is selected for Baseball: Yankees, it puts Baseball in one column and Yankees in the next column to form a list that meets this criteria. If both Baseball and Yankees are in the same row, I don't want it to do anything. I run into trouble in my code if I have a list such as:
Basketball Yankees
Baseball Devil Rays
Basketball Cavaliers...

If I select the criteria 2 for Baseball Yankees, it does not add it to my list. That is because my code sees Basketball first, so it then searches for Yankees. Since it sees Yankees, it does nothing. I would like my code to say if there "Basketball" exists in column P, look in column Q in the same row where Basketball exists, and if Yankees exists in the column Q, do not move the values to columns P and Q.

Please Login or Register  to view this content.

Is there possibly a way to incorporate that into the .Find(What:= field?

Thanks ahead of time,

Lucas

[Andrew-R]

Hi, I've read and re-read this and, sorry, but I really can't work out what you're asking.

Is it possible you could post an example workbook for us to have a look at?

[LCS]

Here is an example workbook. It is oversimplified but I think pretty straightforward. I have a list on the right side. Any time I select 2 it is supposed to place the team and the sport on the list. If that combination of team and sport already exists, it is supposed to place it at the end of the list. If nothing exists it starts the list. (You can see this by deleting the list and reselecting 2 for Yankees Baseball.

I did not insert code for the other fields, but this same code for the other fields would add them to the list. I run into trouble when there is already Yankees in the list and Baseball in the list. It will not put Yankees Baseball onto the end of the list because it does not look for the combination of Yankees Baseball, just the items separately. I am trying to find a way to allow it to do this.

*just a note, if Yankees Baseball is already in the list and 2 is re selected, it will do nothing (neither add, nor remove Yankees Baseball from the list.

I am just struggling to find a way to search for the combination of words in the list.

Thanks,

Lucas

[Andrew-R]

If you're just trying to determine if a particular combination exists then you don't need to do a .Find ...

Please Login or Register  to view this content.

[LCS]

Changing the method worked great. My code now looks like:

Please Login or Register  to view this content.

The only problem with that is it looks for the existence of the combination but does not denote location. I then proceed to have code that says if 1 is chosen, and "Yankees Baseball" is on the list, then remove it from the list. Right now, my code for this part looks like

Please Login or Register  to view this content.

Any suggestions on how to use the find in this situation for the combination of Yankees Baseball?

Thanks!
Lucas

[Andrew-R]

How many rows of data are we talking about? A simple, but tedious, method would be to loop through all of the Yankee matches until we find the Baseball one...

Please Login or Register  to view this content.

[LCS]

That should work. I will try it when I get home and let you know. Just for learning purposes, you said this is simple but tedious. Is there a more sophisticated strategy that you might employ?

[Andrew-R]

I think the best way would be to use filtering to get just the row(s) matching both criteria, but I don't know how to do that off the top of my head and didn't have time at work to teach myself.

I'll look into it, if you want.

[LCS]

Thanks so much for your help. I am on the last steps finishing up my code, which is deleting those entries that match. (a.k.a. the cells in the list that have yankees and baseball next to each other.) my code for selecting the number 1 (from you applied as I need it), is as follows:

Please Login or Register  to view this content.

when I enter the code to delete "baseball",

Please Login or Register  to view this content.

It comes up with an error. Am I off the wall thinking this way?

Lucas

[Andrew-R]

You need to use:

Please Login or Register  to view this content.