vba to copy text to cells based on text in another cell

[cjcass]

Hi,

I'm looking for some vba that will do the following:

I have cells in Sheet1 from cell J101 down (the last cell is variable) that contain text as phrases/sentences.

I have a table in Sheet2 M6:O100. In column M each cell has a keyword in it (eg. dog). Next to each keyword in adjacent cells there are some words in column N (eg. favourite pet) and O (eg. black and white). The table can vary in rows down, ie. it might only have text in cells M6:O30.

I'm looking for some vba that will look at all the phrases/sentences in Sheet1 J101:J### and where it finds a keyword in the phrase (from Sheet2 M6:M##) it populates the corresponding cells in Sheet1 columns E&F with the corresponding words from the cells in Sheet2 N6:O##. The words in the phrases will vary in terms of their case, so cases should not be matched during the vba's search.

So, if Sheet1 cell J175 contains the phrase 'I always walk my dog on Friday' the vba will put 'favourite pet' into cell E175 and 'black and white' into cell F175.
In the event that there are two keywords present in a phrase then it should return the next set of words into cells G175 & H175. After that it should stop. So, if there are three or more keywords it should just return text for the first two found.

Hope this makes sense.
Any help much appreciated.

 

[cjcass]

Just a quick follow up:

• The vba needs to recognise only a keyword that has a space either side, ie. a word in itself and not a keyword that is found in another longer word. So, if the keyword is 'hat' it won't be found in the word 'chatting'
• The cells that are receiving the text from the vba, ie. the cells in Sheet1 Cols E,F,G&H, have data validated dropdown lists in them, so the vba needs to deal with this (I don't think this will be a problem). The text in the dropdown lists are exact matches to the text being transferred.
• Also, it would be good if the vba can skip inserting text into cells (Sheet1 Cols E,F,G&H) that already have a value in them.

 

[cjcass]

For clarity:

• Regarding the 3rd bullet point above - if cell E175 has a value in it but F175 doesn't (sometimes this may happen), then the vba should move onto G175 & H175 and not place the next pair of values into F175 & G175. It needs to populate cells in pairs.

 

[cjcass]

anyone out there got any ideas on this one?
thanks in anticipation.

 

[Peter_SSs]

I suspect that a major stumbling block for potential helpers is that there is no sample data or expected results and trying to build a sample from your written description would be time-consuming.
Have you considered providing what is highlighted bold with XL2BB?

 

[cjcass]

I suspect that a major stumbling block for potential helpers is that there is no sample data or expected results and trying to build a sample from your written description would be time-consuming.
Have you considered providing what is highlighted bold with XL2BB?

Hi,
Thanks for your reply and the info. I've looked at the instructions for XL2BB and it gives me the impression that it will only enable me to upload a range in one wksht - is that correct or have I missed something?
The solution I'm after needs to work across two wkshts - can I upload a simple excel workbook with sample data on this site?
Regards,

 

[Peter_SSs]

I've looked at the instructions for XL2BB and it gives me the impression that it will only enable me to upload a range in one wksht - is that correct

That is not correct. Here is an example: Index Match multiple results skip the blanks

 

[cjcass]

That is not correct. Here is an example: Index Match multiple results skip the blanks

Ok great, I'll give it all a go now.

 

[cjcass]

Have created a sample file and downloaded and installed xl2bb - the ribbon was visible at first but it has now disappeared, tried opening / reopening excel but nothing there now. It is visible in Options > Add Ins and checked but there is nothing visible in the worksheet ribbons, any thoughts on what this might be?

 

[cjcass]

ok sussed it, the file needed unblocking!