• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with Select Case . . .

r121a947

Member
I have a music database. I use codes to record keywords from the lyrics. I have tried to make each code unique, so that I will receive only valid matches, but have not been successful.

I am looking for an easy way to determine whether a particular cell contains a first code (e. g., tk), a second code (e. g., dntk), or both codes. I imagine Select Case would be good for this.

Once it is working for a single cell, I can do a For Next to do the entire database.

Any and all help will be greatly appreciated.

Thanks.
 

r121a947

Member
The J column contains the field to be checked. Every cell in the J column has at least one word. The tk and dntk examples are scattered throughout.

If after checking the routine could populate another cell (M of the same row) with a 0, 1, or 2, depending on the outcome, a sort will easily align things for me.

Thanks for your interest.
 

Attachments

vletm

Excel Ninja
r121a947
How would You name those Your ... eg's?
Do You have ... clear image of Your expected results with Your sample data?
... or could You use Filter?
Screenshot 2020-10-09 at 10.44.38.png
 

r121a947

Member
I don't understand you first question.

My expectation is that the M cell of each row will contain a number, returned from the Select Case determination, and I will sort on the M column to get things properly aligned.

I looked at Filter, but I don't think it will do what I want. I need to determine whether a particular cell contains one of three possible entries:

-- A first code (e. g., tk)
-- A second code (e. g., dntk)
-- Or both codes

In the scenario where I search for tk, dntk would be a false hit, if the cell does not also contain tk. Although not illustrated by the sample data, every cell to be tested would have at least one code. This is the test I am trying to perform.

Thank you for your interest.
 

vletm

Excel Ninja
r121a947
Your I don't understand you first question.
You have written a first code (e. g., tk), a second code (e. g., dntk), or both codes.
Are those fixed codes which You will use? ... if fixed then Okay ... other ways ... How would You modify/name/give/change... those?

Your sample results ...
Seems that there are ... none ... M-column seems to be ... empty ... or how?

Please, write what are You thinking ... I cannot even guess those?
 

r121a947

Member
tk and dntk are examples . . . Wherever in the code they are placed, I can edit the code to insert other pairs, when needed.

M cells are currently empty. My expectation is that the M cell of each row will contain a number, returned from the Select Case determination, and I will sort on the M column to get things properly aligned.

Thanks.
 

vletm

Excel Ninja
r121a947
You wrote that You can edit code ... positive idea
... but You have no idea about that code, especially because there is ... or is there any code?
As Your given sample results for M-column is totally empty then what?
Why select case? ... I won't use that in this kind of case ...
If Your explanation gives all necessary details then as I wrote .. in #9 Reply.
 

r121a947

Member
Are you suggesting that I am incapable of finding the searched for parameters in whatever code results?

Of course M is empty! Nothing has been done to populate it. The return code from the determination will populate M.

If Select Case is not the best way to do this, what is the best way?
 

r121a947

Member
For each determination there will be two codes; one "good" code and one "bad" code. The two codes have common elements (e. g., tk and dntk; tk is good, dntk is bad).

Each cell in the list will match one of three cases:

-- The good code, alone.
-- The bad code, alone.
-- Both codes.

Records matching the first and third cases are good records; records matching the second case are bad records.

Examine for the bad code, first. If it is not present, that is a good record (no record can be in the list without one or both codes; when the bad code is not present, that record is always good because it contains only the good code).

If the bad code is present, then it must be determined whether the good code is present, also. Both codes is a good record; bad code alone is a bad record. Each determination should return a value, which will be written to the M cell of the record's row. Sorting on M will give me good and bad records, which is the goal.
 
Top