VBA Macro Find move Macro work with 5 values but not 808?

[Bobschreck]

I work for a company and we have alot of items that have different color/materials. I receive spreed sheets that have the colors/material in the title but I need it in our format.

Ex: Single Handle Brushed Nickel Faucet
Our Format: Single Handle Faucet, Brushed Nickel

I have compiled a list and put it in VB to take out the info and put it in a different cell when I run it on a small scale it works like a charm oak is oak and rustic oak is rustic oak but when i run it with all of our info the items will put oak instead of rustic oak also puts steel instead of stainless steel and so on. I dunno whats going wrong please help thanks!

Here is the Small scale:
Sub bob()
Dim a As Long, b As Long
Dim c As String, x As String
For a = 2 To Range("A65536").End(xlUp).Row
For b = 1 To 5
c = Choose(b, "Rustic", "Rustic Oak", "Brushed Nickel", "White", "Chrome")
If InStr(Cells(a, 1), c) > 0 Then
x = Application.WorksheetFunction.Substitute(Cells(a, 1), c, "")
Cells(a, 2) = x
Cells(a, 3) = c
End If
Next b
Next a
MsgBox "complete"
End Sub

Here is the large Scale:
Sub bob()
Dim a As Long, b As Long
Dim c As String, x As String
For a = 2 To Range("A65536").End(xlUp).Row
For b = 1 To 808
c = Choose(b, "18 Karat Gold", "Acrylic", "Acrylic and Chrome", "Acrylic Brass", "Acrylic Chrome", "Aegean Mist", "Agata Red", "Agate Red", "Aged Brass", "Aged Bronze", "Aged Chestnut", "Aged Copper", "Aged Ebony", "Aged Gold", "Aged Iron", "Aged Oak", "Aged Pewter", "Aged Umber", "Aged Walnut with Cream Marble", "Alabaster", "Alabaster Dust", "Alabaster White", "Almond", "Almond and Antique Brass", "Almond and Bright Brass", "Almond Galaxy", "Aluminum", "ALU-XT", "Amarillo Sienna", "Amarillo Sienna Matte", "Amber", "Amber Piastra", "Americana Brown", "Ancient Stone", "Anodized Aluminum", "Anodized Gold", "Anodized Silver", "Anthracite", "Antique", "Antique Brass", "Antique Bronze", "Antique Brown", "Antique Cherry", "Antique Copper", "Antique English", "Antique English and Almond", "Antique Gold", "Antique Green", "Antique Iron", "Antique Latte Glaze", "Antique Nickel", "Antique Oak", "Antique Parchment with Brown Calico Marble", "Antique Pecan", "Antique Pewter", _
"Antique Rust", "Antique Silver", "Antique Walnut", "Antique White", "Antique Yellow", "Arctic Granite", "Ash Unfinished", "Auburn Patina", "Autumn Cherry", "Autumn Gold", "Avocado", "Bahama Beige", "Balsa", "Bamboo", "Banana Leaf", "Baracao", "Barley", "Basswood", "Bayberry", "Bayberry/Green", "Bedford Brass", "Beige", "Bella Wood", "Bermuda Coral", "Bermuda Sand", "Berry", "Beveled Edge", "Bianca", "Bianco China", "Biscuit", "Biscuit Satin", "Biscuit/Linen", "Bisque", "Black", "Black and Antique Brass", "Black and Brilliance Pearl Nickel", "Black and Chrome", "Black and Cream Marble", "Black and Polished Brass", "Black and Stainless", "Black and Tan", "Black and White", "Black Forest", "Black Galaxy", "Black Granite", "Black Iron", "Black Nickel", "Black Nickel and White", "Black Opal", "Black Polished Brass", "Black Slate", "Black Stainless", "Black Walnut", "Blackened Bronze", "Blossom White", "Blue", "Blue Pearl", "Bone", "Bordeaux", "Boucle Muslin", _
"Brass", "Brazen Bronze", "Brick Red", "Bright Aluminum", "Bright Brass", "Bright Chrome", "Bright Copper", "Bright Nickel", "Bright Polished Silver", "Bright Silver", "Bright Stainless Steel", "Bright Surface", "Brilliance Brass", "Brilliance Brushed Bronze", "Brilliance Pearl Nickel", "Brilliance Polished Brass", "Brilliance Polished Brass and White", "Brilliance Stainless", "Brilliant Chrome", "Brite Purple", "Bronze", "Bronze & Brass", "Bronze Rust Glaze", "Bronze with Ivory Patina", "Brown", "Brown and Green", "Brown/Tan", "Brushed Aluminum", "Brushed Antique Brass", "Brushed Antique Copper", "Brushed Antique Pewter", "Brushed Black Nickel", "Brushed Brass", "Brushed Brass and Chrome", "Brushed Bronze", "Brushed Chrome", "Brushed Chrome and Brushed Brass", "Brushed Chrome with Polished Chrome", "Brushed Nickel", "Brushed Nickel and Chrome", "Brushed Nickel and Gold", "Brushed Nickel and Polished Brass", "Brushed Nickel and Porcelain", _
"Brushed Nickel and Stainless Steel", "Brushed Nickel and White", "Brushed Pewter", "Brushed Stainless Steel", "Brushed Tin", "Bumblebee", "Burgundy", "Burnet", "Burnished Brass", "Burnt Brick", "Butcher Block", "Cabella Patina", "Cabernet", "Cameo Brass", "Cameo White", "Camouflage", "Canary Yellow", "Candelyght", "Candlelyght", "Cane Sugar", "Canvas", "Cape", "Cashmere", "Cerulean Blue", "Champagne", "Charcoal", "Charleston Almond", "Charleston White", "Cherry", "Cherry Maple", "Cherry Wood", "Chestnut", "China Bianco", "Chocolate", "Chrome", "Chrome and 18 Karat Gold", "Chrome and Black", "Chrome and Brass", "Chrome and Brilliance Polished Brass", "Chrome and Brushed Nickel", "Chrome and Glacier", "Chrome and Gold", "Chrome and Platinum", "Chrome and Polished Brass", "Chrome and Porcelain", "Chrome and Satin Nickel", "Chrome and Soft Black", "Chrome and White", "Chrome and White and Clear", "Chrome Fittings", "Chrome Plated Brass", "Chrome Plated Steel", _
"Chrome-Nickel", "Chrome-plated", "Cider Maple", "Cinder", "Cinnamon", "Claret Wine", "Classic Gold and Pebbled Cream", "Classic Turquoise", "Clay", "Clear", "Clear and Chrome", "Clear and Polished Brass", "Clear Blue", "Clear Crystal and Chrome", "Clear Crystal and Gold", "Clear Frost", "Clear Glass", "Coated White", "Cobalt Blue", "Coffee", "Cognac", "Cognac Patina", "Colonial Maple", "Colonial Red", "Colonial White", "Copper", "Cornflower", "Cotton", "Country Blue Granite", "Country Grey", "Country White", "Crackle", "Crackled Bronze", "Crackled Bronze with Silver", "Craftsman", "Crane White", "Cream", "Cream White", "Creamy Yellow", "Crystal", "Crystal and Bright Brass", "Crystal and Polished Chrome", "Crystal and Satin Chrome", "Dark Antique", "Dark Antique Bronze", "Dark Antique English", "Dark Antique Walnut", "Dark Bronze", "Dark Cherry", "Dark Cherry with Cream Marble", "Dark Chocolate", "Dark Copper", "Dark Gray", "Dark Grey", "Dark Mahogany", _
"Dark Oak with Black Granite", "Dark Red Mahogany", "Dark Restoration Bronze", "Dark Stone", "Dark Tan", "Dark Walnut", "Daydream", "Desert Bloom", "Desert Gold", "Diamond", "Distressed Antique Nickel", "Distressed Bronze", "Distressed Nickel", "Distressed Oil Rubbed Bronze", "Distressed Oil-Rubbed Bronze", "Distressed Venetian Bronze", "Distressed White", "Dresden Blue", "Driftwood", "Dull Antique Brass", "Durango", "Dusty Rose", "Early American", "Earth Brown", "Ebony", "E-Granite", "Elegant Brass", "Ember", "Emperador Dark", "Emperador Light", "Empire Gold with Silver", "English Bronze", "Espresso", "Euro Bronze", "Euro White", "Evergreen", "Expresso Brown", "Faceted Crystal Chrome", "Faceted Crystal Chrome", "Fawn Beige", "Fawn Maple", "Flat Black", "Flat Chrome", "Flemish", "Florence Patina", "Forest Green", "Forever Brass", "Fortepierre", "Fossil Stone", "French Bronze", "French Gold", "French Ivory", "Frost", "Frost White", "Frosted", "Frosted Amber", _
"Frosted and Satin Chrome", "Frosted Blue", "Frosted Cobalt", "Frosted Copper", "Frosted Crystal", "Frosted Green", "Frosted Metallic Gold", "Frosted Metallic Silver", "Frosted Violet", "Frosted White", "Fruitwood", "Galvanized", "Glacier", "Glacier Grey", "Glacier White", "Glass", "Gloss Black", "Glossy Black", "Gold", "Gold Leaf", "Golden Bronze", "Golden Hill", "Golden Oak", "Golden Pecan", "Goldenrod", "Granite", "Granito", "Graphite", "Gray", "Gray Granite", "Green", "Green Granite", "Green Onyx", "Grey", "Grey Blue", "Grey Brown", "Grey White", "Gun", "Gunstock", "Habana Night", "Hammered Brass", "Hammered Bronze", "Hammered Iron", "Hammered Mirror", "Hammered Nickel", "Hammered Polished Chrome", "Hammered Weathered Copper", "Harvest Gold", "Havana", "Heather", "Heirloom White", "Heritage", "Heritage Bronze", "Heron Blue", "Honey Brass", "Honey Onyx", "Honeydew", "Hot Pink", "Hunter Green", "Ice", "Ice Grey", "Ice White", "Inca Brass", "Indian Grass", _
"Indian Green", "Indian Red", "Indigo", "Infinity Brushed Nickel", "Infinity Polished Brass", "Infinity Satin Nickel", "Infinity Satin Nickel and Polished Brass", "Innocent Blush", "Innocent Blush/Zinfandel", "Iron", "Iron Black", "Iron Cobalt", "Iron Oxide", "Iron Oxide with Gold Leaf Accents", "Iron Weed", "Ivory", "Ivory Brass", "Ivy", "Jasmine", "Jersey Cream", "Light Antique Walnut", "Light Brass", "Light Brass and Oak", "Light Brown with Cream Marble", "Light Green Frost", "Light Grey", "Light Mink", "Light Platinum", "Light Stone", "Light Turquoise", "Lily", "Limed Oak", "Limestone", "Linen", "Linen and Biscuit", "Loganberry", "Lustrous Satin Nickel", "Mage", "Magenta", "Mahogany", "Mahogany Bronze", "Maple", "Maple Wood", "Marble", "Maroon", "Matte Black", "Matte Chrome", "Matte Copper", "Matte Nickel", "Matte Pewter", "Matte White", "Medium Bronze", "Medium Oak", "Medium Oak with Black Granite", "Medium Stone", "Merlot", "Merlot with Black Granite", _
"Metalic", "Metallic Gray", "Mexican Sand", "Midnight Black", "Ming Green", "Mirror", "Mirror Finish", "Mirror French Gold", "Mirrored Copper", "Mocha", "Mocha Bronze", "Mocha Spice Cherry", "Modern Bronze", "Montero", "Monterrey Beige", "Monterrey Beige with Silver", "Montesol", "Moss Green", "Muslin", "Napoli", "Natural", "Natural Cherry", "Natural Iron", "Natural Maple", "Natural Stone", "Navy", "Navy Blue", "Nero", "Nero Marquina", "Neutral", "New Orleans Blue", "Nickel", "Nickel and Chrome", "Nickel and Matte Black", "Nickel Bronze", "Nickel Chrome", "Nickel-Chrome", "Night Sky", "Noche Travertine", "Nutmeg", "Oak", "Oak and Burnished Brass", "Oak and Polished Brass", "Oatmeal", "Off- White", "Oil Rub Bronze", "Oil Rubbed Bronze", "Oiled Bronze", "Old Bronze", "Old Bronze and Black", "Old English", "Old English Brass", "Old World Black", "Old World Bronze", "Old World Copper", "Olive", "Onyx", "Orange", "Orchid", "Overlord Flower", "Oxford", "Oxide Brass", _
"Oxide Brass and Silver", "Oxide Brass with Silver", "Oxide Bronze", "Oxide Bronze with Silver", "Oxide Gold", "Oyster", "Parchment", "Pas", "Pastel", "Patina", "Peach Bisque", "Peach Blossom", "Peach/Coral", "Peachblow", "Pearl", "Pearl Nickel", "Pearl Nickel and Brass", "Pearl Nickel and Chrome", "Pearl Nickel and Polished Brass", "Pebble", "Pecan", "Pecan with Black Granite", "Peened and Bright Surface", "Peened Surface", "Pergamon", "Perlato Svevo", "Pewter", "Pewter and Satin Nickel", "Pewter French Iron", "Pine", "Pink", "Plain Edge", "Plantinesque", "Platinum", "Platinum and Chrome", "Platinum and Platinum", "Platinum Nickel", "Polar White", "Polish Brass", "Polished and Lacquered Brass", "Polished Brass", "Polished Brass and Almond", "Polished Brass and Black", "Polished Brass and Bone", "Polished Brass and Bright Stainless Steel", "Polished Brass and Linen", "Polished Brass and Polished Chrome", "Polished Brass and Satin Chrome", _
"Polished Brass and Satin Nickel", "Polished Brass and White", "Polished Bronze", "Polished Chrome", "Polished Chrome and Brass", "Polished Chrome and Polished Brass", "Polished Chrome and UltraBrass", "Polished Copper", "Polished Gold", "Polished Nickel", "Polished Stainless", "Polished Stainless Steel", "Polymer", "Poplar", "Porcelain", "Porcelain and Chrome", "Porcelain and Copper", "Porcelain and Polished Brass", "Prairie Clover", "Provincial", "Purple", "Pvd Brass", "Quadro", "Queen Onyx", "Rain Forest", "Raspberry", "Rattan", "Red", "Red Mahogany", "Red Oak", "Redwood", "Regency Blue", "Regency Brass", "Renaissance Bronze", "Rhapsody Blue", "Riversand", "Romawood", "Rose Beige", "Rosewood", "Rough Brass", "Rough Bronze", "Rough Chrome", "Rough Chrome Plated", "Rough Nickel", "Rough Plate", "Roya", "Royal Blue", "Rubbed Bronze", "Rust", "Rust Copper", "Rust Glaze", "Rust Patina", "Rust with Gold", "Rustic Black Nickel", "Rustic Brass", "Rustic Bronze", _
"Rustic Brown", "Rustic Brushed Brass", "Rustic Copper", "Rustic Iron", "Rustic Metal", "Rustic Nickel", "Rustic Pewter", "Saddle", "Saffron", "Sand", "Sand Beige Marble", "Sandbar", "Sandstone", "Sapele", "Sapphire Blue", "Satin", "Satin / Brass Mix", "Satin and Chrome", "Satin and Polished Brass", "Satin Black", "Satin Brass", "Satin Brass and Black", "Satin Bronze", "Satin Bronze with French Gold", "Satin Chrome", "Satin Chrome with Polished Chrome", "Satin Finish", "Satin Honey", "Satin Nickel", "Satin Nickel and Chrome", "Satin Nickel and Polished Brass", "Satin Nickel with Black", "Satin Nickel with Polished Brass", "Satin Nickel with Polished Nickel", "Satin Silver", "Satin Stainless", "Satin Stainless Steel", "Satin White", "Satine", "Satine and Ivory", "Satine and Polished Brass", "Satinox", "Sea Salt", "Seafoam", "Seafoam Green", "Seashell", "Sedona Beige", "Serengeti", "Serpeggiante", "Serpentine Bronze", "Shanxi Black", "Shell", "Shell Rose", "Shell White", _
"Sienna", "Sienna Brown Marble", "Silk", "Silver", "Silver Grey Hammertone", "Silver Nickel and Gold", "Silver Oxide", "Silver Pewter", "Silvered Black", "Silvia Fadl", "Sky Blue", "Skylight", "Slate", "Solid Beech", "Solid Birch", "Solid Brass and Chrome", "Spice Maple", "Spring", "Spruce Green", "Stainless", "Stainless and Black", "Stainless and Matte Black", "Stainless Steel", "Stainless Steel and Black", "Stainless with Black Accents", "Steel", "Steel Optik", "Steel Optik and Black", "Sterling", "Sterling Infinity", "Sterling Infinity Finish", "Sterling Nickel", "Sterling Silver", "SUNDOWNER", "Sunlight", "Tahiti Desert", "Tahiti Gray", "Tahiti Ivory", "Tahiti Matrix", "Tahiti Rose", "Tahiti Sand", "Tahiti Terra", "Tahiti White", "Tan", "Tea Green", "Teak", "Teal Blue", "Tempered", "Tender Grey", "Terra Cotta", "test", "Textured Rust", "Textured Stone Ivory", "Textured Stone Noce", "Textured White", "Thunder Grey", "Timberline", "Tin", "Tin and White", "Tint", _
"Tobacco", "Traditional Cherry", "Travertine", "Truffle Ash", "Tumbled Bronze", "Turqueise", "Tuscan Brass", "Tuscan Bronze", "Tuscan Copper", "Tuscan Patina", "Tuscan Rock", "Tuscan Romance", "Tuscan Wheat", "Tweed", "Twilight Blue", "UltraBrass", "UltraSteel", "Umber Bronze", "Uncoated Brass", "Unfinished", "Vanilla", "Vellum", "Velour Chrome", "Venetian Bronze", "Venetian Pink", "Venitian Bronze", "Verde Green", "Verdigris", "Victorian Rose", "Vintage Oak with Black Granite", "Viridian Slate", "Volterra Bronze", "Walnut", "Warm Mahongany", "Warm Walnut", "Warm White", "Washed Antique", "Washed Bronze", "Weathered", "Weathered Black", "Weathered Brass", "Weathered Bronze", "Weathered Copper", "Weathered Copper and Brass", "Weathered Iron", "Weathered Nickel", "Weathered Nickel Copper", "Weathered Platinum", "Weathered White", "Wenge", "Wenge Stain", "Westwood", "White", "White (Chrome nut)", "White (Chrome nuts)", "White and Bright Brass", "White and Chrome", _
"White and Polished Brass", "White and Polished Chrome", "White and Satin Nickel", "White and Weathered Copper and Brass", "White and White (Chrome nuts)", "White Heat", "White Oak", "White Onyx", "White Slate", "White Travertino", "White/Antique Brass", "Whitewash Pecan", "Whitewashed Pecan", "Wicker", "Wild Bronze", "Wild Rose", "Wine Red", "Winter Wheat", "Wood", "Wood and Weathered Copper and Brass", "Wood Cherry", "Wood Maple", "Wood Oak", "Wrought Iron", "Yellow", "Zebra Veneer", "Zinc", "Zinc Plated", "Zinc Primer")
If InStr(Cells(a, 1), c) > 0 Then
x = Application.WorksheetFunction.Substitute(Cells(a, 1), c, "")
Cells(a, 2) = x
Cells(a, 3) = c
End If
Next b
Next a
MsgBox "complete"
End Sub

 

[texasalynn]

are you sure that the choose function will accept that many items?

 

[texasalynn]

the choose function will accept up to 30 as far as I know. You might want to use a case statement instead

 

[Bobschreck]

it worked with 114 the problem is I don't know how to make a case statement

 

[texasalynn]

here is a sample of a case

Select Case ws
    Case "Sheet1"
        Sheets("Sheet2").Activate
    Case "Sheet2"
        Sheets("Sheet3").Activate
    Case "Sheet3"
        Sheets("Sheet4").Activate
    Case Else
        Exit Sub