Syntax error on VBA with find/replace function

[bob122]

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

Can someone please help?

 

[Rick Rothstein]

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...

[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 & *******", "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]

 

[bob122]

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...

[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 & *******", "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]

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.

 

[bob122]

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.