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

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
683
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top