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

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
643
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

cjcass

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

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

Well-known Member
Joined
Oct 27, 2011
Messages
643
Office Version
  1. 2016
Platform
  1. Windows
anyone out there got any ideas on this one?
thanks in anticipation.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,886
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

cjcass

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

Well-known Member
Joined
Oct 27, 2011
Messages
643
Office Version
  1. 2016
Platform
  1. Windows
ok sussed it, the file needed unblocking!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,381
Messages
5,853,490
Members
431,593
Latest member
AAAaahhh

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
Top