Syntax error on VBA with find/replace function

Rick Rothstein

MrExcel MVP
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.
I have combined everything into one macro below (and I kept your category breakouts so you could make changes more easily in the future), but I also changed one of your search criteria from xlPart to xlWhole meaning the searched for text must be the only text in the cell in order to be found. I did this because some of your search text might be located in larger pieces of text but not be what needs to be changed... in particular, your search for the letter "T" (in last posted macro in Message #7 ) would be troublesome if the xlPart criteria is used as any word not being searched for that contained the letter "T" would be affected. If your searched for text could actually be contained in a cell with more text in it, then the loop will have to be rewritten and the macro would become much, much slower. Anyway, with the above proviso in mind, here is the macro I came up...
Code:
[table="width: 500"]
[tr]
	[td]Sub Multi_FindReplace()
  Dim sht As Worksheet
  Dim fndList(1 To 8) As Variant
  Dim rplcList(1 To 8) As Variant
  Dim R As Long, C As Long
  
  fndList(1) = Array("AIRE", "ASIAN FOODS", "BABY - BASICS", "BABY - E", "BABY - NAPPIES", "BABY FOOD", "BABY FORMULA", "BBQ", "BISCUITS - CHOCOLATE", "BISCUITS - CRISPBRD & CRACKER", "BISCUITS - MULTIPACK", "BISCUITS - PLAIN & FANCY", "BISCUITS - SNACKING", "BRANCH REQUISITES", "BRD MIX", "BRKFAST - CERLS", "BRKFAST - MUESLI & OATS")
  rplcList(1) = Array("12", "5", "10", "9", "9", "10", "10", "11", "2", "2", "2", "2", "2", "99", "7", "1", "1")
  
  fndList(2) = 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(2) = Array("7", "6", "6", "6", "98", "4", "4", "6", "4", "4", "4", "4", "4", "4", "4", "4", "5")
  
  fndList(3) = 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(3) = Array("5", "3", "10", "9", "9", "7", "13", "12", "7", "7", "8", "5", "7", "7", "10", "7", "7", "97")
  
  fndList(4) = 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(4) = Array("3", "12", "11", "7", "5", "10", "10", "10", "1", "11", "8", "13", "12", "5", "11", "96", "6", "7", "12", "12", "12", "12", "22")
  
  fndList(5) = 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(5) = Array("7", "3", "9", "9", "ThI Iave5", "2", "1", "5", "5", "9", "7", "12", "5", "5", "9", "11", "11", "11", "11", "11", "11", "11")

  fndList(6) = 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(6) = 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")

  fndList(7) = 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(7) = Array("5", "13", "1", "1", "1", "1", "1", "8", "6", "10", "2", "8", "9", "13", "12", "8", "5", "6", "90")

  fndList(8) = Array("CLNERS", "COFFEE", "T")
  rplcList(8) = Array("12", "2", "2")
  
  Application.ScreenUpdating = False
  On Error Resume Next
  'Loop through each worksheet in ActiveWorkbook one at a time
  For Each sht In Worksheets
  'Loop through each array in fndList
    For R = 1 To UBound(fndList)
      'Loop through each element in the fndList array
      For C = LBound(fndList(R)) To UBound(fndList(R))
        sht.Cells.SpecialCells(xlConstants).Replace _
              What:=fndList(R)(C), Replacement:=rplcList(R)(C), _
              LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
              SearchFormat:=False, ReplaceFormat:=False
      Next C
    Next R
  Next sht
  On Error GoTo 0
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]
 
Last edited:

bob122

New Member
I have combined everything into one macro below (and I kept your category breakouts so you could make changes more easily in the future), but I also changed one of your search criteria from xlPart to xlWhole meaning the searched for text must be the only text in the cell in order to be found. I did this because some of your search text might be located in larger pieces of text but not be what needs to be changed... in particular, your search for the letter "T" (in last posted macro in Message #7 ) would be troublesome if the xlPart criteria is used as any word not being searched for that contained the letter "T" would be affected. If your searched for text could actually be contained in a cell with more text in it, then the loop will have to be rewritten and the macro would become much, much slower. Anyway, with the above proviso in mind, here is the macro I came up...
Code:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Multi_FindReplace()
  Dim sht As Worksheet
  Dim fndList(1 To 8) As Variant
  Dim rplcList(1 To 8) As Variant
  Dim R As Long, C As Long
  
  fndList(1) = Array("AIRE", "ASIAN FOODS", "BABY - BASICS", "BABY - E", "BABY - NAPPIES", "BABY FOOD", "BABY FORMULA", "BBQ", "BISCUITS - CHOCOLATE", "BISCUITS - CRISPBRD & CRACKER", "BISCUITS - MULTIPACK", "BISCUITS - PLAIN & FANCY", "BISCUITS - SNACKING", "BRANCH REQUISITES", "BRD MIX", "BRKFAST - CERLS", "BRKFAST - MUESLI & OATS")
  rplcList(1) = Array("12", "5", "10", "9", "9", "10", "10", "11", "2", "2", "2", "2", "2", "99", "7", "1", "1")
  
  fndList(2) = 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(2) = Array("7", "6", "6", "6", "98", "4", "4", "6", "4", "4", "4", "4", "4", "4", "4", "4", "5")
  
  fndList(3) = 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(3) = Array("5", "3", "10", "9", "9", "7", "13", "12", "7", "7", "8", "5", "7", "7", "10", "7", "7", "97")
  
  fndList(4) = 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(4) = Array("3", "12", "11", "7", "5", "10", "10", "10", "1", "11", "8", "13", "12", "5", "11", "96", "6", "7", "12", "12", "12", "12", "22")
  
  fndList(5) = 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(5) = Array("7", "3", "9", "9", "ThI Iave5", "2", "1", "5", "5", "9", "7", "12", "5", "5", "9", "11", "11", "11", "11", "11", "11", "11")

  fndList(6) = 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(6) = 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")

  fndList(7) = 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(7) = Array("5", "13", "1", "1", "1", "1", "1", "8", "6", "10", "2", "8", "9", "13", "12", "8", "5", "6", "90")

  fndList(8) = Array("CLNERS", "COFFEE", "T")
  rplcList(8) = Array("12", "2", "2")
  
  Application.ScreenUpdating = False
  On Error Resume Next
  'Loop through each worksheet in ActiveWorkbook one at a time
  For Each sht In Worksheets
  'Loop through each array in fndList
    For R = 1 To UBound(fndList)
      'Loop through each element in the fndList array
      For C = LBound(fndList(R)) To UBound(fndList(R))
        sht.Cells.SpecialCells(xlConstants).Replace _
              What:=fndList(R)(C), Replacement:=rplcList(R)(C), _
              LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
              SearchFormat:=False, ReplaceFormat:=False
      Next C
    Next R
  Next sht
  On Error GoTo 0
  Application.ScreenUpdating = True
End Sub[/TD]
[/TR]
</tbody>[/TABLE]
Thank you so much! I really appreciate it. Yes you can see that trouble I ran into with the "T" problem haha. The same happened with "clners" and "coffee" which is why I had to leave those 3 at the end. And yeah I'll be adding a lot more words in the future, so thank you for doing that for me. I've spent a solid 15-20 hours in the last 3 days trying to get this sorted so thank you for all the help.
 

Rick Rothstein

MrExcel MVP
Thank you so much! I really appreciate it. Yes you can see that trouble I ran into with the "T" problem haha. The same happened with "clners" and "coffee" which is why I had to leave those 3 at the end.
Was I correct in assuming the text you will be searching for is the only text that will be in the cell (in other words, was my switch from xlPart to xlWhole correct)?



And yeah I'll be adding a lot more words in the future, so thank you for doing that for me.
Note that you can add additional words to the last group... the one with fndListI(8) and rplcList(8)... however, if you add a new group (its index would be one more then the previous last index... that is, the old last index was 8 so the new index would be 9), then you must change the following Dim statements to match...

Dim fndList(1 To 8) As Variant
Dim rplcList(1 To 8) As Variant

in other words, change the red highlighted 8's to 9's to match the index for the newly added group.
 
Last edited:

bob122

New Member
Was I correct in assuming the text you will be searching for is the only text that will be in the cell (in other words, was my switch from xlPart to xlWhole correct)?
Yes your assumption was correct =) I'll definitely be using the macro that you wrote to prevent any future stuff ups so thank you. (My very very limited knowledge of script words must have been too obvious =D)



Note that you can add additional words to the last group... the one with fndListI(8) and rplcList(8)... however, if you add a new group (its index would be one more then the previous last index... that is, the old last index was 8 so the new index would be 9), then you must change the following Dim statements to match...

Dim fndList(1 To 8) As Variant
Dim rplcList(1 To 8) As Variant

in other words, change the red highlighted 8's to 9's to match the index for the newly added group.
Sweet, thank you.
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top