Select Case statements after Search String not working

[rdowney79]

I am attempting to use a search string that will take priority over some select statements. The Search String statements run correctly, but the Select Statements get skipped over and dont populate.

Please Login or Register  to view this content.

Thanks,
Ray

[tfurnivall]

Negative logic sucks, doesn't it.

Your code says:

Please Login or Register  to view this content.

Well, if a cell DOES contain one of those somethings, then it most certainly does NOT contain any of the other somethings, so the only thing that will cause your nifty piece of code to execute will be if the cells contain NONE of the suggested strings. I don't think that's what you wanted.

Please Login or Register  to view this content.

may be a better and easier to read way of expressing your intent!

HTH

Tony

[AB33]

Assuming that the search lines work. Try to use one of the lines for the select case range

Please Login or Register  to view this content.

[rdowney79]

thanks, his code update appears to work but has highlighted the fact that my search lines do not work correctly. Any suggestions of having the search string look for cells that contain the identified values within the text and return the entire cell contents if a match occurs?

Please Login or Register  to view this content.

[tfurnivall]

Please Login or Register  to view this content.

If Anyone of those conditions is true, then you want to do the Select statement, right?

Consider the case where Instr(1,pCell.Value,"TSC") is > 0.

Both of the other two conditions will return FALSE, and as a result the entire condition (joined by AND operators) will also return FALSE.

That's what I meant when I said that negative logic sucks!

Instr will return 0 or above, so the whole <>0 is unnecessary - it sucks you into the negative logic thingy. Test for what you want (or at least for what you can get easily), and then use an ELSE clause if necessary.

HTH

Tony

[rdowney79]

Thank you for you assistance, but this is not what I'm attempting to do. What I'm hoping to accomplish is to have the InStr statements handle exceptions to the Select statements. So in the case of Instr(1,pCell.Value,"TSC") is > 0, if the contents of the pCell does contain the string "TSC", such as '83TSC91780', then I need the code to return the entire contents of pCell. Otherwise if the contents of pCell did not contain the "TSC" string, I need the code to perform the Select Statements. In a different example, if the contents of pCell did not contain the string "TSC", such as ('97UNKN9170'), then I would need the Case Is = 10 Range("D" & pCell.Row).FormulaR1C1 = "= LEFT(RC[-1],7)" Select statement to be performed. Hope this is clearer than mud.

Thanks again!

Ray

[tfurnivall]

Sort of like this?

Please Login or Register  to view this content.

I think the issue all along has been When to do something, much more than What to do!

I hope that waters are less turbid, now!

Tony

[shg]

Maybe ...

Please Login or Register  to view this content.

[rdowney79]

shg, that appears to work great. Ill do some more testing here in a bit to verify, but thank you!

[shg]

You're welcome.