I'm Struggling to loop this VBA code

nimlee

New Member
Joined
Oct 6, 2019
Messages
4
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,323
Office Version
365
Platform
Windows
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

New Member
Joined
Oct 6, 2019
Messages
4
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

New Member
Joined
Oct 6, 2019
Messages
4
Sorry Peter that data didn't stay in the arrangement I want. Please review the website link.

Kind Regards
David
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,323
Office Version
365
Platform
Windows
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
CD
1Original DescriptionShort Description
2
3CALTEX CARINE
4CALTEX CARINEFuel
5CALTEX NORTHLANDS BALCATTAFuel
6CALTEXFuel
7CARINE CALTEXFuel
8COFFEE CLUB WHITFORDFood - Take Away
9COFFEE CLUB WHITFORDFood - Take Away
10COLES INNALOOFood - House Hold
11COLES INNALOOFood - House Hold
12COLES BALCATTAFood - House Hold
13COLES BALCATTAFood - House Hold
14MCDONALDS TUART HILLFood - Take Away
15MCDONALDS TUART HILLFood - Take Away
16MCDONALDS WARWICKFood - Take Away
17MYER JOONDALUPClothes
18MYER JOONDALUPClothes
19
20MYER MORLEYClothes
21WOOLWORTHS MIRRABOOKA** Add to list
22MYER MORLEYClothes
Record
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,323
Office Version
365
Platform
Windows
.. 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
CD
1Original DescriptionShort Description
2
3CALTEX CARINE
4CALTEX CARINEFuel
5CALTEX NORTHLANDS BALCATTAFuel
6CALTEXFuel
7CARINE CALTEXFuel
8COFFEE CLUB WHITFORDFood - Take Away
9COFFEE CLUB WHITFORDFood - Take Away
10COLES INNALOOFood - House Hold
11COLES INNALOOFood - House Hold
12COLES BALCATTAFood - House Hold
13COLES BALCATTAFood - House Hold
14MCDONALDS TUART HILLFood - Take Away
15MCDONALDS TUART HILLFood - Take Away
16MCDONALDS WARWICKFood - Take Away
17MYER JOONDALUPClothes
18MYER JOONDALUPClothes
19
20MYER MORLEYClothes
21WOOLWORTHS MIRRABOOKA** Add to list
22MYER MORLEYClothes
Record
 
Last edited:

nimlee

New Member
Joined
Oct 6, 2019
Messages
4
Hi Peter
This does what I wanted. I am very grateful. Thank you very much.
Kind Regards
David
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,323
Office Version
365
Platform
Windows
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. :)
 

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top