Syntax error on VBA with find/replace function

B

bob122

New Member
Joined
May 10, 2018
Messages
17
Hi, I'm trying to write a code in VBA to do a mass find/replace. Basically I want certain words to be replaced with numbers instead. However, I keep getting a syntax error and I can't see where. Here is the code:

Sub Multi_FindReplace()'PURPOSE: Find & Replace a list of text/values throughout entire workbook'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault




Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long




fndList = Array(”AIRE”, ”ASIAN FOODS”, ”BABY - BASICS”, ”BABY - E”, ”BABY - NAPPIES”, ”BABY FOOD”, ”BABY FORMULA”, ”BBQ”, ”BISCUITS - CHOCOLATE”, ”BISCUITS - CRISPBRD & *******”, ”BISCUITS - MULTIPACK”, ”BISCUITS - PLAIN & FANCY”, ”BISCUITS - SNACKING”, ”BRANCH REQUISITES”, ”BRD MIX”, ”BRKFAST - CERLS”, ”BRKFAST - MUESLI & OATS”, ”CAKE MIX & BAKING AIDS”, ”CANNED FISH”, ”CANNED FRUIT”, ”CANNED VEGETABLES”, ”CHEESE PRE-PACKED ENTERTAINING”, ”CHIPS - MULTIPACKS”, ”CHIPS - SHARING”, ”CLNERS”, ”COFFEE”, ”CONES & TOPPINGS & WATERICES”, ”CONFECTIONERY - BARS”, ”CONFECTIONERY - BLOCKS”, ”CONFECTIONERY - CHOC BITES”, ”CONFECTIONERY - GIFTING”, ”CONFECTIONERY - GUM & MEDICATED”, ”CONFECTIONERY - NOVELTY”, ”CONFECTIONERY - SHAREPACKS”, ”CONFECTIONERY - SUGAR”, ”COOK IN SAUCES”, ”COOKING OILS”, ”CORDIAL”, ”COSMETICS”, ”DEODORANT & TALC”, ”DIET & SPORT NUTRITION”, ”DINNERWARE”, ”DISHWASHING DETERGENT”, ”DISINFECTANTS”, ”DISPOSABLE PICNICWARE”, ”DRIED FRUIT & NUTS”, ”ELECTRICAL”, ”EUROPN FOODS”, ”FAMILY SOCKS”, ”FAMILY UNDERWR”, ”FEMININE HYGIENE”, ”FLOUR”, ”FOOD STORAGE”, ”FOOD WRAPS BAGS AND STORAGE”, ”FRUIT JUICE - LONG LIFE”, ”GARBAGE BAGS”, ”GARDEN - E”, ”GLASSWARE”, ”GRAVY”, ”HAIR ACCESSORIES”, ”HAIR COLOUR”, ”HAIR E”, ”HLTH FOODS”, ”HOME IMPROVEMENT & MOTORING”, ”HOSIERY”, ”HOUSEHOLD CLNING”, ”HOUSEHOLD STORAGE”, ”INDIAN FOODS”, ”INSECTICIDES”, ”ISB SUPPLIES - DRY RAW MATERIAL”, ”JELLY & PUDDINGS”, ”******* TOOLS AND PREPARATION”, ”LAUNDRY - FABRIC E”, ”LAUNDRY - LIQUIDS”, ”LAUNDRY - POWDERS”, ”LAUNDRY - SOAKERS & BLCH”, ”LONGLIFE MILK & SOY DRINKS”, ”LUNCH AND HYDRATION”, ”LUNCHBOX DRINKS”, ”MEDICINAL”, ”MENS TOILETRIES & RAZORS”, ”MEXICAN FOODS”, ”MILK ADDITIVES”, ”MUESLI BARS”, ”NEW ZLAND FOODS”, ”NOODLES”, ”ORAL E”, ”OVENWARE”, ”PAPER TOWEL”, ”PASTA SAUCE & CHEESE”, ”PASTA”, ”PERSONAL WASH”, ”PET NEEDS - CAT FOOD DRY”, ”PET NEEDS - CAT FOOD WET”, ”PET NEEDS - DOG FOOD DRY”, ”PET NEEDS - DOG FOOD WET”, ”PET NEEDS - DOG TRTS”, ”PET NEEDS - E & ACCESSORIES”, ”PET NEEDS - SMALL ANIMAL”, ”PREPARED MLS LONG LIFE”, ”PRINTER/INK”, ”RDY TO GO MLS”, ”RECIPE BASES”, ”RELISHES & PICKLES”, ”RESALE CAKE”, ”RESALE INTERNATIONAL BRD”, ”REUSABLE SHOPPING BAGS”, ”RICE”, ”SAUCES”, ”SHOE E”, ”SIDE DISHES”, ”SKIN E”, ”SMOKING ACCESSORIES”, ”SNACK - NUTS”, ”SOFT DRINKS - BOTTLES & CANS”, ”SOFT DRINKS - COLD DRINK”, ”SOFT DRINKS - ENERGY”, ”SOFT DRINKS - MINERAL WATER”, ”SOFT DRINKS - MIXERS”, ”SOFT DRINKS - SPORTS & ICE T”, ”SOFT DRINKS - WATER”, ”SOUP - CANNED”, ”SOUP - PACKET”, ”SPICES & COOKING NEEDS”, ”SPONGES/SCOURERS”, ”SPRDS - HONEY”, ”SPRDS - JAM”, ”SPRDS - OTHER”, ”SPRDS - PNUT BUTTER”, ”SPREADS - OTHER”, ”STATIONERY - BASIC”, ”SUGAR”, ”SUN E”, ”T AND COFFEE ACCESSORIES”, ”T”, ”TECH”, ”TISSUES”, ”TOILET CLNERS”, ”TOILET ROLLS”, ”TOYS”, ”UK AND IRISH FOODS”, ”VINEGAR MAYO & DRESSINGS”, ”WRAPPING - BAKEHOUSE”)
rplcList = Array("12", "5", "10", "9", "9", "10", "10", "11", "2", "2", "2", "2", "2", "99", "7", "1", "1", "7", "6", "6", "6", "98", "5", "5", "12", "2", "6", "4", "4", "4", "4", "4", "4", "4", "4", "5", "5", "3", "10", "9", "9", "7", "13", "12", "7", "7", "8", "5", "7", "7", "10", "7", "7", "97", "3", "12", "11", "7", "5", "10", "10", "10", "1", "11", "8", "13", "12", "5", "11", "96", "6", "7", "12", "12", "12", "12", "22", "7", "3", "9", "9", "5", "2", "1", "5", "5", "9", "7", "12", "5", "5", "9", "11", "11", "11", "11", "11", "11", "11", "6", "8", "6", "5", "6", "95", "94", "93", "5", "6", "12", "6", "10", "92", "4", "3", "91", "44", "3", "3", "44", "3", "6", "6", "5", "13", "1", "1", "1", "1", "1", "8", "6", "10", "2", "2", "8", "9", "13", "12", "8", "5", "6", "90")




'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht


Next x




End Sub
Click to expand...
Can someone please help?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Every quote mark in your fndList array is incorrect... you must use ASCII 34 quote marks, not the "stylized" quote marks you actually used. I think I have correctly assembled the actual code line you should use here...
Code: 
[table="width: 500"]
[tr]
	[td]fndList = Array("�AIRE"�, "�ASIAN FOODS"�, "�BABY - BASICS"�, "�BABY - E"�, "�BABY - NAPPIES"�, "�BABY FOOD"�, "�BABY FORMULA"�, "�BBQ"�, "�BISCUITS - CHOCOLATE"�, "�BISCUITS - CRISPBRD & *******"�, "�BISCUITS - MULTIPACK"�, "�BISCUITS - PLAIN & FANCY"�, "�BISCUITS - SNACKING"�, "�BRANCH REQUISITES"�, "�BRD MIX"�, "�BRKFAST - CERLS"�, "�BRKFAST - MUESLI & OATS"�, "�CAKE MIX & BAKING AIDS"�, "�CANNED FISH"�, "�CANNED FRUIT"�, "�CANNED VEGETABLES"�, "�CHEESE PRE-PACKED ENTERTAINING"�, "�CHIPS - MULTIPACKS"�, "�CHIPS - SHARING"�, "�CLNERS"�, "�COFFEE"�, "�CONES & TOPPINGS & WATERICES"�, "�CONFECTIONERY - BARS"�, "�CONFECTIONERY - BLOCKS"�, "�CONFECTIONERY - CHOC BITES"�, "�CONFECTIONERY - GIFTING"�, "�CONFECTIONERY - GUM & MEDICATED"�, "�CONFECTIONERY - NOVELTY"�, "�CONFECTIONERY - SHAREPACKS"�, "�CONFECTIONERY - SUGAR"�, "�COOK IN SAUCES"�, "�COOKING OILS"�, "�CORDIAL"�, "�COSMETICS"�, "�DEODORANT & TALC"�, "�DIET & SPORT NUTRITIO
N"�, "�DINNERWARE"�, "�DISHWASHING DETERGENT"�, "�DISINFECTANTS"�, "�DISPOSABLE PICNICWARE"�, "�DRIED FRUIT & NUTS"�, "�ELECTRICAL"�, "�EUROPN FOODS"�, "�FAMILY SOCKS"�, "�FAMILY UNDERWR"�, "�FEMININE HYGIENE"�, "�FLOUR"�, "�FOOD STORAGE"�, "�FOOD WRAPS BAGS AND STORAGE"�, "�FRUIT JUICE - LONG LIFE"�, "�GARBAGE BAGS"�, "�GARDEN - E"�, "�GLASSWARE"�, "�GRAVY"�, "�HAIR ACCESSORIES"�, "�HAIR COLOUR"�, "�HAIR E"�, "�HLTH FOODS"�, "�HOME IMPROVEMENT & MOTORING"�, "�HOSIERY"�, "�HOUSEHOLD CLNING"�, "�HOUSEHOLD STORAGE"�, "�INDIAN FOODS"�, "�INSECTICIDES"�, "�ISB SUPPLIES - DRY RAW MATERIAL"�, "�JELLY & PUDDINGS"�, "�******* TOOLS AND PREPARATION"�, "�LAUNDRY - FABRIC E"�, "�LAUNDRY - LIQUIDS"�, "�LAUNDRY - POWDERS"�, "�LAUNDRY - SOAKERS & BLCH"�, "�LONGLIFE MILK & SOY DRINKS"�, "�LUNCH AND HYDRATION"�, "�LUNCHBOX DRINKS"�, "�MEDICINAL"�, "�MENS TOILETRIES & RAZORS"�, "�MEXICAN FOODS"�, "�MILK ADDITIVES"�, "�MUESLI BARS"�, "�NEW
 ZLAND FOODS"�, "�NOODLES"�, "�ORAL E"�, "�OVENWARE"�, "�PAPER TOWEL"�, "�PASTA SAUCE & CHEESE"�, "�PASTA"�, "�PERSONAL WASH"�, "�PET NEEDS - CAT FOOD DRY"�, "�PET NEEDS - CAT FOOD WET"�, "�PET NEEDS - DOG FOOD DRY"�, "�PET NEEDS - DOG FOOD WET"�, "�PET NEEDS - DOG TRTS"�, "�PET NEEDS - E & ACCESSORIES"�, "�PET NEEDS - SMALL ANIMAL"�, "�PREPARED MLS LONG LIFE"�, "�PRINTER/INK"�, "�RDY TO GO MLS"�, "�RECIPE BASES"�, "�RELISHES & PICKLES"�, "�RESALE CAKE"�, "�RESALE INTERNATIONAL BRD"�, "�REUSABLE SHOPPING BAGS"�, "�RICE"�, "�SAUCES"�, "�SHOE E"�, "�SIDE DISHES"�, "�SKIN E"�, "�SMOKING ACCESSORIES"�, "�SNACK - NUTS"�, "�SOFT DRINKS - BOTTLES & CANS"�, "�SOFT DRINKS - COLD DRINK"�, "�SOFT DRINKS - ENERGY"�, "�SOFT DRINKS - MINERAL WATER"�, "�SOFT DRINKS - MIXERS"�, "�SOFT DRINKS - SPORTS & ICE T"�, "�SOFT DRINKS - WATER"�, "�SOUP - CANNED"�, "�SOUP - PACKET"�, "�SPICES & COOKING NEEDS"�, "�SPONGES/SCOURERS"�, "�SPRDS - HONEY"�, â
€�SPRDS - JAM"�, "�SPRDS - OTHER"�, "�SPRDS - PNUT BUTTER"�, "�SPREADS - OTHER"�, "�STATIONERY - BASIC"�, "�SUGAR"�, "�SUN E"�, "�T AND COFFEE ACCESSORIES"�, "�T"�, "�TECH"�, "�TISSUES"�, "�TOILET CLNERS"�, "�TOILET ROLLS"�, "�TOYS"�, "�UK AND IRISH FOODS"�, "�VINEGAR MAYO & DRESSINGS"�, "�WRAPPING - BAKEHOUSE"�)[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
Rick Rothstein said:
Every quote mark in your fndList array is incorrect... you must use ASCII 34 quote marks, not the "stylized" quote marks you actually used. I think I have correctly assembled the actual code line you should use here...
Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]fndList = Array("�AIRE"�, "�ASIAN FOODS"�, "�BABY - BASICS"�, "�BABY - E"�, "�BABY - NAPPIES"�, "�BABY FOOD"�, "�BABY FORMULA"�, "�BBQ"�, "�BISCUITS - CHOCOLATE"�, "�BISCUITS - CRISPBRD & *******"�, "�BISCUITS - MULTIPACK"�, "�BISCUITS - PLAIN & FANCY"�, "�BISCUITS - SNACKING"�, "�BRANCH REQUISITES"�, "�BRD MIX"�, "�BRKFAST - CERLS"�, "�BRKFAST - MUESLI & OATS"�, "�CAKE MIX & BAKING AIDS"�, "�CANNED FISH"�, "�CANNED FRUIT"�, "�CANNED VEGETABLES"�, "�CHEESE PRE-PACKED ENTERTAINING"�, "�CHIPS - MULTIPACKS"�, "�CHIPS - SHARING"�, "�CLNERS"�, "�COFFEE"�, "�CONES & TOPPINGS & WATERICES"�, "�CONFECTIONERY - BARS"�, "�CONFECTIONERY - BLOCKS"�, "�CONFECTIONERY - CHOC BITES"�, "�CONFECTIONERY - GIFTING"�, "�CONFECTIONERY - GUM & MEDICATED"�, "�CONFECTIONERY - NOVELTY"�, "�CONFECTIONERY - SHAREPACKS"�, "�CONFECTIONERY - SUGAR"�, "�COOK IN SAUCES"�, "�COOKING OILS"�, "�CORDIAL"�, "�COSMETICS"�, "�DEODORANT & TALC"�, "�DIET & SPORT NUTRITIO
N"�, "�DINNERWARE"�, "�DISHWASHING DETERGENT"�, "�DISINFECTANTS"�, "�DISPOSABLE PICNICWARE"�, "�DRIED FRUIT & NUTS"�, "�ELECTRICAL"�, "�EUROPN FOODS"�, "�FAMILY SOCKS"�, "�FAMILY UNDERWR"�, "�FEMININE HYGIENE"�, "�FLOUR"�, "�FOOD STORAGE"�, "�FOOD WRAPS BAGS AND STORAGE"�, "�FRUIT JUICE - LONG LIFE"�, "�GARBAGE BAGS"�, "�GARDEN - E"�, "�GLASSWARE"�, "�GRAVY"�, "�HAIR ACCESSORIES"�, "�HAIR COLOUR"�, "�HAIR E"�, "�HLTH FOODS"�, "�HOME IMPROVEMENT & MOTORING"�, "�HOSIERY"�, "�HOUSEHOLD CLNING"�, "�HOUSEHOLD STORAGE"�, "�INDIAN FOODS"�, "�INSECTICIDES"�, "�ISB SUPPLIES - DRY RAW MATERIAL"�, "�JELLY & PUDDINGS"�, "�******* TOOLS AND PREPARATION"�, "�LAUNDRY - FABRIC E"�, "�LAUNDRY - LIQUIDS"�, "�LAUNDRY - POWDERS"�, "�LAUNDRY - SOAKERS & BLCH"�, "�LONGLIFE MILK & SOY DRINKS"�, "�LUNCH AND HYDRATION"�, "�LUNCHBOX DRINKS"�, "�MEDICINAL"�, "�MENS TOILETRIES & RAZORS"�, "�MEXICAN FOODS"�, "�MILK ADDITIVES"�, "�MUESLI BARS"�, "�NEW
 ZLAND FOODS"�, "�NOODLES"�, "�ORAL E"�, "�OVENWARE"�, "�PAPER TOWEL"�, "�PASTA SAUCE & CHEESE"�, "�PASTA"�, "�PERSONAL WASH"�, "�PET NEEDS - CAT FOOD DRY"�, "�PET NEEDS - CAT FOOD WET"�, "�PET NEEDS - DOG FOOD DRY"�, "�PET NEEDS - DOG FOOD WET"�, "�PET NEEDS - DOG TRTS"�, "�PET NEEDS - E & ACCESSORIES"�, "�PET NEEDS - SMALL ANIMAL"�, "�PREPARED MLS LONG LIFE"�, "�PRINTER/INK"�, "�RDY TO GO MLS"�, "�RECIPE BASES"�, "�RELISHES & PICKLES"�, "�RESALE CAKE"�, "�RESALE INTERNATIONAL BRD"�, "�REUSABLE SHOPPING BAGS"�, "�RICE"�, "�SAUCES"�, "�SHOE E"�, "�SIDE DISHES"�, "�SKIN E"�, "�SMOKING ACCESSORIES"�, "�SNACK - NUTS"�, "�SOFT DRINKS - BOTTLES & CANS"�, "�SOFT DRINKS - COLD DRINK"�, "�SOFT DRINKS - ENERGY"�, "�SOFT DRINKS - MINERAL WATER"�, "�SOFT DRINKS - MIXERS"�, "�SOFT DRINKS - SPORTS & ICE T"�, "�SOFT DRINKS - WATER"�, "�SOUP - CANNED"�, "�SOUP - PACKET"�, "�SPICES & COOKING NEEDS"�, "�SPONGES/SCOURERS"�, "�SPRDS - HONEY"�, â
€�SPRDS - JAM"�, "�SPRDS - OTHER"�, "�SPRDS - PNUT BUTTER"�, "�SPREADS - OTHER"�, "�STATIONERY - BASIC"�, "�SUGAR"�, "�SUN E"�, "�T AND COFFEE ACCESSORIES"�, "�T"�, "�TECH"�, "�TISSUES"�, "�TOILET CLNERS"�, "�TOILET ROLLS"�, "�TOYS"�, "�UK AND IRISH FOODS"�, "�VINEGAR MAYO & DRESSINGS"�, "�WRAPPING - BAKEHOUSE"�)[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Thank you, I realized the error after posting. I changed them all to the correct quotation mark however I still get the same error. So I shortened the code and only did 10-20 words at a time then it worked each time. So I believe I am getting the syntax error because the code is too long. Do you perhaps know how to overcome this error?
 
Upvote 0
Good thought Rick.

If you still have the problem, try breaking up the lines using line continuation (eg: "BABY FOOD", _ ) the underscore character: to continue on the next line.
There IS a limit to the number of line continuation characters that you can use, so try to split each line in about half and you should be good.
 
Upvote 0
bob122 said:
Thank you, I realized the error after posting. I changed them all to the correct quotation mark however I still get the same error. So I shortened the code and only did 10-20 words at a time then it worked each time. So I believe I am getting the syntax error because the code is too long. Do you perhaps know how to overcome this error?
Click to expand...
I think you may have some other, hidden non-standard ASCII characters in your quoted text. When I copy/pasted the fixed code line I posted, back into the VB editor, there were strange characters in the text (usually that happens when there are non-standard ASCII characters in the text and the VB editor does not know how to display them). Did you create your list from inside a word processor? If so, I think you captured some of the formatting codes the word processor uses to display text inside of its own displays.
 
Upvote 0
Okay so I fixed the errors, so each of the following quotes work fine on there own. But how can I combine them all into one script so that I don't have to run each one separately? The research I have done tells me that I need to add _ but this doesn't work for me / I can't get it working.

Sub Multi_FindReplace()'PURPOSE: Find & Replace a list of text/values throughout entire workbook'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault




Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long




fndList = Array("AIRE", "ASIAN FOODS", "BABY - BASICS", "BABY - E", "BABY - NAPPIES", "BABY FOOD", "BABY FORMULA", "BBQ", "BISCUITS - CHOCOLATE", "BISCUITS - CRISPBRD & *******", "BISCUITS - MULTIPACK", "BISCUITS - PLAIN & FANCY", "BISCUITS - SNACKING", "BRANCH REQUISITES", "BRD MIX", "BRKFAST - CERLS", "BRKFAST - MUESLI & OATS")
rplcList = Array("12", "5", "10", "9", "9", "10", "10", "11", "2", "2", "2", "2", "2", "99", "7", "1", "1")




'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht


Next x




End Sub
Click to expand...

Sub Multi_FindReplace()'PURPOSE: Find & Replace a list of text/values throughout entire workbook'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault




Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long




fndList = Array("CAKE MIX & BAKING AIDS", "CANNED FISH", "CANNED FRUIT", "CANNED VEGETABLES", "CHEESE PRE-PACKED ENTERTAINING", "CHIPS - MULTIPACKS", "CHIPS - SHARING", "CONES & TOPPINGS & WATERICES", "CONFECTIONERY - BARS", "CONFECTIONERY - BLOCKS", "CONFECTIONERY - CHOC BITES", "CONFECTIONERY - GIFTING", "CONFECTIONERY - GUM & MEDICATED", "CONFECTIONERY - NOVELTY", "CONFECTIONERY - SHAREPACKS", "CONFECTIONERY - SUGAR", "COOK IN SAUCES")
rplcList = Array("7", "6", "6", "6", "98", "4", "4", "6", "4", "4", "4", "4", "4", "4", "4", "4", "5")




'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht


Next x




End Sub
Click to expand...

Sub Multi_FindReplace()'PURPOSE: Find & Replace a list of text/values throughout entire workbook'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault




Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long




fndList = Array("COOKING OILS", "CORDIAL", "COSMETICS", "DEODORANT & TALC", "DIET & SPORT NUTRITION", "DINNERWARE", "DISHWASHING DETERGENT", "DISINFECTANTS", "DISPOSABLE PICNICWARE", "DRIED FRUIT & NUTS", "ELECTRICAL", "EUROPN FOODS", "FAMILY SOCKS", "FAMILY UNDERWR", "FEMININE HYGIENE", "FLOUR", "FOOD STORAGE", "FOOD WRAPS BAGS AND STORAGE")
rplcList = Array("5", "3", "10", "9", "9", "7", "13", "12", "7", "7", "8", "5", "7", "7", "10", "7", "7", "97")




'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht


Next x




End Sub
Click to expand...

Sub Multi_FindReplace()'PURPOSE: Find & Replace a list of text/values throughout entire workbook'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault




Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long




fndList = Array("FRUIT JUICE - LONG LIFE", "GARBAGE BAGS", "GARDEN - E", "GLASSWARE", "GRAVY", "HAIR ACCESSORIES", "HAIR COLOUR", "HAIR E", "HLTH FOODS", "HOME IMPROVEMENT & MOTORING", "HOSIERY", "HOUSEHOLD CLNING", "HOUSEHOLD STORAGE", "INDIAN FOODS", "INSECTICIDES", "ISB SUPPLIES - DRY RAW MATERIAL", "JELLY & PUDDINGS", "******* TOOLS AND PREPARATION", "LAUNDRY - FABRIC E", "LAUNDRY - LIQUIDS", "LAUNDRY - POWDERS", "LAUNDRY - SOAKERS & BLCH", "LONGLIFE MILK & SOY DRINKS")
rplcList = Array("3", "12", "11", "7", "5", "10", "10", "10", "1", "11", "8", "13", "12", "5", "11", "96", "6", "7", "12", "12", "12", "12", "22")




'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht


Next x




End Sub
Click to expand...

Sub Multi_FindReplace()'PURPOSE: Find & Replace a list of text/values throughout entire workbook'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault




Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long




fndList = Array("LUNCH AND HYDRATION", "LUNCHBOX DRINKS", "MEDICINAL", "MENS TOILETRIES & RAZORS", "MEXICAN FOODS", "MILK ADDITIVES", "MUESLI BARS", "NEW ZLAND FOODS", "NOODLES", "ORAL E", "OVENWARE", "PAPER TOWEL", "PASTA SAUCE & CHEESE", "PASTA", "PERSONAL WASH", "PET NEEDS - CAT FOOD DRY", "PET NEEDS - CAT FOOD WET", "PET NEEDS - DOG FOOD DRY", "PET NEEDS - DOG FOOD WET", "PET NEEDS - DOG TRTS", "PET NEEDS - E & ACCESSORIES", "PET NEEDS - SMALL ANIMAL")
rplcList = Array("7", "3", "9", "9", "5", "2", "1", "5", "5", "9", "7", "12", "5", "5", "9", "11", "11", "11", "11", "11", "11", "11")




'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht


Next x




End Sub
Click to expand...

Sub Multi_FindReplace()'PURPOSE: Find & Replace a list of text/values throughout entire workbook'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault




Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long




fndList = Array("PREPARED MLS LONG LIFE", "PRINTER/INK", "RDY TO GO MLS", "RECIPE BASES", "RELISHES & PICKLES", "RESALE CAKE", "RESALE INTERNATIONAL BRD", "REUSABLE SHOPPING BAGS", "RICE", "SAUCES", "SHOE E", "SIDE DISHES", "SKIN E", "SMOKING ACCESSORIES", "SNACK - NUTS", "SOFT DRINKS - BOTTLES & CANS", "SOFT DRINKS - COLD DRINK", "SOFT DRINKS - ENERGY", "SOFT DRINKS - MINERAL WATER", "SOFT DRINKS - MIXERS", "SOFT DRINKS - SPORTS & ICE T", "SOFT DRINKS - WATER", "SOUP - CANNED", "SOUP - PACKET")
rplcList = Array("6", "8", "6", "5", "6", "95", "94", "93", "5", "6", "12", "6", "10", "92", "4", "3", "91", "44", "3", "3", "44", "3", "6", "6")




'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht


Next x




End Sub
Click to expand...

Sub Multi_FindReplace()'PURPOSE: Find & Replace a list of text/values throughout entire workbook'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault




Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long




fndList = Array("SPICES & COOKING NEEDS", "SPONGES/SCOURERS", "SPRDS - HONEY", "SPRDS - JAM", "SPRDS - OTHER", "SPRDS - PNUT BUTTER", "SPREADS - OTHER", "STATIONERY - BASIC", "SUGAR", "SUN E", "T AND COFFEE ACCESSORIES", "TECH", "TISSUES", "TOILET CLNERS", "TOILET ROLLS", "TOYS", "UK AND IRISH FOODS", "VINEGAR MAYO & DRESSINGS", "WRAPPING - BAKEHOUSE")
rplcList = Array("5", "13", "1", "1", "1", "1", "1", "8", "6", "10", "2", "8", "9", "13", "12", "8", "5", "6", "90")




'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht


Next x




End Sub
Click to expand...

Sub Multi_FindReplace()'PURPOSE: Find & Replace a list of text/values throughout entire workbook'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault




Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long




fndList = Array("CLNERS", "COFFEE", "T")
rplcList = Array("12", "2", "2")




'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht


Next x




End Sub
Click to expand...
 
Upvote 0
How about
Code: 
Sub Multi_FindReplace() 'PURPOSE: Find & Replace a list of text/values throughout entire workbook'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

   Dim sht As Worksheet
   Dim fndList As Variant
   Dim rplcList As Variant
   Dim x As Long
   
   fndList = Array("AIRE", "ASIAN FOODS", "BABY - BASICS", "BABY - E", "BABY - NAPPIES", "BABY FOOD", "BABY FORMULA", "BBQ", "BISCUITS - CHOCOLATE", "BISCUITS - CRISPBRD & *******", "BISCUITS - MULTIPACK", "BISCUITS - PLAIN & FANCY", "BISCUITS - SNACKING", "BRANCH REQUISITES", "BRD MIX", "BRKFAST - CERLS", "BRKFAST - MUESLI & OATS", _
      "CAKE MIX & BAKING AIDS", "CANNED FISH", "CANNED FRUIT", "CANNED VEGETABLES", "CHEESE PRE-PACKED ENTERTAINING", "CHIPS - MULTIPACKS", "CHIPS - SHARING", "CONES & TOPPINGS & WATERICES", "CONFECTIONERY - BARS", "CONFECTIONERY - BLOCKS", "CONFECTIONERY - CHOC BITES", "CONFECTIONERY - GIFTING", "CONFECTIONERY - GUM & MEDICATED", "CONFECTIONERY - NOVELTY", "CONFECTIONERY - SHAREPACKS", "CONFECTIONERY - SUGAR", "COOK IN SAUCES", _
      "COOKING OILS", "CORDIAL", "COSMETICS", "DEODORANT & TALC", "DIET & SPORT NUTRITION", "DINNERWARE", "DISHWASHING DETERGENT", "DISINFECTANTS", "DISPOSABLE PICNICWARE", "DRIED FRUIT & NUTS", "ELECTRICAL", "EUROPN FOODS", "FAMILY SOCKS", "FAMILY UNDERWR", "FEMININE HYGIENE", "FLOUR", "FOOD STORAGE", "FOOD WRAPS BAGS AND STORAGE", _
      "FRUIT JUICE - LONG LIFE", "GARBAGE BAGS", "GARDEN - E", "GLASSWARE", "GRAVY", "HAIR ACCESSORIES", "HAIR COLOUR", "HAIR E", "HLTH FOODS", "HOME IMPROVEMENT & MOTORING", "HOSIERY", "HOUSEHOLD CLNING", "HOUSEHOLD STORAGE", "INDIAN FOODS", "INSECTICIDES", "ISB SUPPLIES - DRY RAW MATERIAL", "JELLY & PUDDINGS", "******* TOOLS AND PREPARATION", "LAUNDRY - FABRIC E", "LAUNDRY - LIQUIDS", "LAUNDRY - POWDERS", "LAUNDRY - SOAKERS & BLCH", "LONGLIFE MILK & SOY DRINKS", _
      "LUNCH AND HYDRATION", "LUNCHBOX DRINKS", "MEDICINAL", "MENS TOILETRIES & RAZORS", "MEXICAN FOODS", "MILK ADDITIVES", "MUESLI BARS", "NEW ZLAND FOODS", "NOODLES", "ORAL E", "OVENWARE", "PAPER TOWEL", "PASTA SAUCE & CHEESE", "PASTA", "PERSONAL WASH", "PET NEEDS - CAT FOOD DRY", "PET NEEDS - CAT FOOD WET", "PET NEEDS - DOG FOOD DRY", "PET NEEDS - DOG FOOD WET", "PET NEEDS - DOG TRTS", "PET NEEDS - E & ACCESSORIES", "PET NEEDS - SMALL ANIMAL", _
      "PREPARED MLS LONG LIFE", "PRINTER/INK", "RDY TO GO MLS", "RECIPE BASES", "RELISHES & PICKLES", "RESALE CAKE", "RESALE INTERNATIONAL BRD", "REUSABLE SHOPPING BAGS", "RICE", "SAUCES", "SHOE E", "SIDE DISHES", "SKIN E", "SMOKING ACCESSORIES", "SNACK - NUTS", "SOFT DRINKS - BOTTLES & CANS", "SOFT DRINKS - COLD DRINK", "SOFT DRINKS - ENERGY", "SOFT DRINKS - MINERAL WATER", "SOFT DRINKS - MIXERS", "SOFT DRINKS - SPORTS & ICE T", "SOFT DRINKS - WATER", "SOUP - CANNED", "SOUP - PACKET", _
      "SPICES & COOKING NEEDS", "SPONGES/SCOURERS", "SPRDS - HONEY", "SPRDS - JAM", "SPRDS - OTHER", "SPRDS - PNUT BUTTER", "SPREADS - OTHER", "STATIONERY - BASIC", "SUGAR", "SUN E", "T AND COFFEE ACCESSORIES", "TECH", "TISSUES", "TOILET CLNERS", "TOILET ROLLS", "TOYS", "UK AND IRISH FOODS", "VINEGAR MAYO & DRESSINGS", "WRAPPING - BAKEHOUSE", "CLNERS", "COFFEE", "T")
   rplcList = Array("12", "5", "10", "9", "9", "10", "10", "11", "2", "2", "2", "2", "2", "99", "7", "1", "1", "7", "6", "6", "6", "98", "4", "4", "6", "4", "4", "4", "4", "4", "4", "4", "4", "5", "5", "3", "10", "9", "9", "7", "13", "12", "7", "7", "8", "5", "7", "7", "10", "7", "7", "97", "3", "12", "11", "7", "5", "10", "10", "10", "1", "11", "8", "13", "12", "5", "11", "96", "6", "7", "12", "12", "12", "12", "22", "7", "3", "9", "9", "5", "2", "1", "5", "5", "9", "7", "12", "5", "5", "9", "11", "11", "11", "11", "11", "11", "11", _
      "6", "8", "6", "5", "6", "95", "94", "93", "5", "6", "12", "6", "10", "92", "4", "3", "91", "44", "3", "3", "44", "3", "6", "6", "5", "13", "1", "1", "1", "1", "1", "8", "6", "10", "2", "8", "9", "13", "12", "8", "5", "6", "90", "12", "2", "2")
   
   'Loop through each item in Array lists
   For x = LBound(fndList) To UBound(fndList)
      'Loop through each worksheet in ActiveWorkbook
      For Each sht In ActiveWorkbook.Worksheets
         sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
      Next sht
   Next x
End Sub
PS when posting code please use code tags, the # icon in the reply window, rather than quotes.
 
Upvote 0
It worked perfectly! Thank you so much!

Ps: Sorry about posting as quotes, now I know the correct way to post at least :biggrin:
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Populate Dates and Data With Criteria
2
Replies
11
Views
164
Alex Blakenburg
A
K
  • Solved
Populate Date With Criteria
Replies
6
Views
115
Peter_SSs
Peter_SSs
S
  • Question Question
Using output of LAMBDA within a function
Replies
3
Views
187
Phuoc
P
WERNER SLABBERT
  • Question Question
Run-time error 70 after code changed
Replies
3
Views
70
RoryA
RoryA
H
  • Question Question
Transposing Data - Evaluating formula to an array error.
Replies
1
Views
42
Skyybot
Skyybot

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

Share this page

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