Selection Change macro to OFFSET

[MannStewart]

Hello there,


1. I'm trying to write a Selection Change macro to OFFSET the activecell to different cells (columns-Q,S,T) on each row based on the Text(s) contained in the user input cell in column-O. But my code doesn't seem to work. I attached a image below to illustrate my situation, with remarks that specify the criteria needed for how the specified keywords must be found in column-O, including the large/small caps or not they appear, in order for the OFFSET to move. Data in my workbook is from row 5 through to 5000.


SelChngOFFSET.PNG


This the last method i used in the most recent attempt at making the code to achieve above, all have failed. I also experimented with InStr & that failed miserably.

Please Login or Register  to view this content.

2. The second problem I have is with column-A.
I use the ISNUMBER function for column-A to return a short ID based on the text string in column-O, all is well till when the keywords in O is more than one keyword to locate. When the Description string in column-O is only 1 keyword to find, say the keyword to lookout for is only "RECYCLE", the ISNUMBER returns "REC" without any error. But i dont know how to use ISNUMBER to also lookout for & cover all the other possibilities shown in above image, ie. when ID should lookout for in the input cell strings that might contain 2 or more keywords simultaneously, if it meets that possibility it can't correctly return the correct ID.

Here is my effort ISNUMBER example of the failed function for more than 1 keyword to return an ID, example when I need the cell in A5 to return "PWG" if O5 contains ALL 3 keywords "Return","to","via", but it anyhow returns "PWG" even if the user has only typed "Return" withoput typing "to" and "via"

Please Login or Register  to view this content.

Hope someone can help me with above 2 issues,

Stewart.

[Vraag en antwoord]

Nice picture. It is difficult to enter code in it.

Hm, I can't get the code placed in the normal way, so even a picture.

Knipsel.PNG

[MannStewart]

Hi Vraag

Great thanks for the code, which i just tested and seems working, I plan to test it more & hope it works totally for all the keywords.

Can you guide me on my issue no.2 also ? Because the column A cannot cover the remaining of all possibilities of keywords with 2-words, 3-words combinations, so the outcome in A is not the full possibilities of IDs outcomes at the moment ..


Stewart

[Vraag en antwoord]

See the yellow banner.

[MannStewart]

sorry, i didn't follow, which yellow banner ? Did you mean i should post a sample workbook ? Kindly have a look my issue for ISNUMBER(SEARCH) function in A5:A20, with the dilemma under remark in the attached sample worksheet.