I'm Struggling to loop this VBA code

[nimlee]

Hi,
I have searched for days to find this code as I am not knowledgeable enough to write it myself. I want to expand this VBA code from a single use code to a multiple use code. eg looping through the range to the final row.

This code works exactly what I want it to do, but only for one row, I would like it to loop though to the final row.
This being Sheets ("Record") Range("C2:C500").
Range ("C8") on sheets ("Menu") is the first cell of the key words list to search for. I require this line of code to search for multiple key words in this range. eg loop through the keywords.
Range ("E8") on sheets ("Menu") is also a list (range) of words (text values). This list is the words that will be placed in the next cell over when a keyword match is found.
Can you help it would be appreciated.


Sub Start_Search_Button()


Dim x As Variant
Dim y As Variant


x = Sheets("Menu").Range("C8").Value
y = Sheets("Menu").Range("E8")


Sheets("Record").Activate
Range("C2:C500").Find(What:=x, LookIn:=xlValues).Activate
ActiveCell.Offset(0, 1).Activate
ActiveCell = y


End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

Whilst I think we can help you loop the code to what you want it would be best to first clarify a few things.

1. Are you sure the code does what you want for the first line? I ask because you talked about matching "words" but if C8 on menu was "cat" and C2:C500 on Record contained "caterpillar" or "scathing" then your code would put the menu E8 value next to that. Is that what you want or are you looking for a complete match of a word?

2. Do the cells in Record just contain single words? For example, if the word being searched for was again "cat", will the Record column just have "cat" or might it have "a cat sat on the mat"?

3. For the word in C8 of Menu, might it occur more than once in C2:C500 of Record? If it can occur more than once to you want the E8 value next to each occurrence?

4. When we resolve the above questions (& there may be more) it may turn out that there is a quicker way to achieve the result than looping through a row at a time. If that turns out to be the case, would that be acceptable?
Also, does it have to be VBA? If we could get the result with a formula, would that be acceptable?

Much of the above would be clarified if you were able to post a small set of dummy sample data and the expected results here in the forum. My signature block below has help with how to do that.

 

[nimlee]

Hi Peter,
Thanks for your help.
I did download the file you suggested however I could not understand what to do in the short time I have to answer you. So I added two screen shots to my website to get around this issue. The link is below. I also added some data text here as an after thought.

[url]https://theriggerspen.com.au/mr-excel-help-file/[/URL]

1. I would never have picked that up until it caused me issues . Thank you for your insight. I would require an exact match.

2. The cells in the record worksheet range would contain multiple words. Please see screen shot for the example.

3. Yes the key word in cell C8 would appear multiple times and the new short description from E8 would be placed in the range multiple times also. Please see screen shot for the example.

4. When we resolve the above questions (& there may be more) it may turn out that there is a quicker way to achieve the result than looping through a row at a time. If that turns out to be the case, would that be acceptable? Yes this would be acceptable.
Also, does it have to be VBA? No.
If we could get the result with a formula, would that be acceptable? Yes this would be acceptable.


Here is an example of data and how I visioned the spreadsheet set up.


Sheet 1 = "Record"
'Column 1 will be the data I want the key words to sort through. I wish to add to this list and it will continue to grow.
'Column 2 will be the short description placed from the keyword search result.





Original Description Short Description

CALTEX CARINE Fuel
CALTEX CARINE Fuel
CALTEX NORTHLANDS BALCATTA Fuel
CALTEX Fuel
CARINE CALTEX Fuel
COFFEE CLUB WHITFORD Food - Take Away
COFFEE CLUB WHITFORD Food - Take Away
COLES INNALOO Food - House Hold
COLES INNALOO Food - House Hold
COLES BALCATTA Food - House Hold
COLES BALCATTA Food - House Hold
MCDONALDS TUART HILL Food - Take Away
MCDONALDS TUART HILL Food - Take Away
MCDONALDS WARWICK Food - Take Away
MYER JOONDALUP Clothes
MYER JOONDALUP Clothes
MYER MORLEY Clothes
MYER MORLEY Clothes





Sheet 2 = Menu
'Column 1 is the keywords I want to find in column 1 on sheet 1. I wish to add to this list and it will continue to grow.
'Column 2 is the short description I wish to have place in column 2 on sheet 1. I wish to add to this list and it will continue to grow.

Key Word Search Add to Shortened Column
CALTEX Fuel
COFFEE CLUB Food - Take Away
COLES Food - House Hold
MCDONALDS Food - Take Away
MYER Clothes

 

[nimlee]

Sorry Peter that data didn't stay in the arrangement I want. Please review the website link.

Kind Regards
David

 

[Peter_SSs]

Sorry Peter that data didn't stay in the arrangement I want.

Never mind, by clicking 'Reply With Quote' on post 3 I can see the layout - but it is why the Add-Ins suggested are much better (like below )

We can do this with a formula, just make the ranges (where I have used $20) big enough to cover the amount of data you are ever likely to use in the Menu sheet.

Excel Workbook
	C	D
1	Original Description	Short Description
2
3	CALTEX CARINE	Fuel
4	CALTEX CARINE	Fuel
5	CALTEX NORTHLANDS BALCATTA	Fuel
6	CALTEX	Fuel
7	CARINE CALTEX	Fuel
8	COFFEE CLUB WHITFORD	Food - Take Away
9	COFFEE CLUB WHITFORD	Food - Take Away
10	COLES INNALOO	Food - House Hold
11	COLES INNALOO	Food - House Hold
12	COLES BALCATTA	Food - House Hold
13	COLES BALCATTA	Food - House Hold
14	MCDONALDS TUART HILL	Food - Take Away
15	MCDONALDS TUART HILL	Food - Take Away
16	MCDONALDS WARWICK	Food - Take Away
17	MYER JOONDALUP	Clothes
18	MYER JOONDALUP	Clothes
19
20	MYER MORLEY	Clothes
21	WOOLWORTHS MIRRABOOKA	** Add to list
22	MYER MORLEY	Clothes
Record

 

[Peter_SSs]

.. just make the ranges (where I have used $20) big enough to cover the amount of data you are ever likely to use in the Menu sheet.

You can avoid even worrying about that if you turn columns C:E (at least) in Menu into a formal table.

You could try this with a copy of your workbook.

1. On Menu, select C7:E(end of data) or if there are more columns of associated data that you have not shown us, include those columns too but still start at the heading row
2. Insert ribbon tab -> Insert -> Table -> My table has headers -> OK
3. Formulas ribbon tab -> Name Manager -> Click on (probably) Table1 -> Edit... -> Name: LookupTable -> OK -> Close
4. Use a formula like this on Record where the names in the [ ] match the headings in columns C & E of the table in Menu.

As you add further entries in the table on Menu, the table automatically expands and the formulas on Record don't need any attention as they will automatically pick up any new values.

Excel Workbook
	C	D
1	Original Description	Short Description
2
3	CALTEX CARINE	Fuel
4	CALTEX CARINE	Fuel
5	CALTEX NORTHLANDS BALCATTA	Fuel
6	CALTEX	Fuel
7	CARINE CALTEX	Fuel
8	COFFEE CLUB WHITFORD	Food - Take Away
9	COFFEE CLUB WHITFORD	Food - Take Away
10	COLES INNALOO	Food - House Hold
11	COLES INNALOO	Food - House Hold
12	COLES BALCATTA	Food - House Hold
13	COLES BALCATTA	Food - House Hold
14	MCDONALDS TUART HILL	Food - Take Away
15	MCDONALDS TUART HILL	Food - Take Away
16	MCDONALDS WARWICK	Food - Take Away
17	MYER JOONDALUP	Clothes
18	MYER JOONDALUP	Clothes
19
20	MYER MORLEY	Clothes
21	WOOLWORTHS MIRRABOOKA	** Add to list
22	MYER MORLEY	Clothes
Record

 

[nimlee]

Hi Peter
This does what I wanted. I am very grateful. Thank you very much.
Kind Regards
David

 

[Peter_SSs]

Hi Peter
This does what I wanted. I am very grateful. Thank you very much.
Kind Regards
David

You're welcome David. Thanks for the follow-up.