Hi,
I am trying to check to see if two cells in Sheets("CHECKING") or (ws2), correspond with values in Sheets("Category Hierarchy (5)") or ws3. In row 1 of ws3 there are "Departments" and below that department from row 2 onwards are categories and these categories are unique values to only one department.
The value in Cell.offset(0,-2) is the category and the value in cell.offset(0,-3) is the department. We need to make sure that the category in ws2 in the column of the matched department in ws3. i.e. make sure there is not a mismatched category to a department.
The following code works fine but it takes a long time to loop through all the cells in rng1.
Is there a quicker way to increase speed? See below for code and samples of data from both ws.
Here is some sample data from ws2:
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>
Here the sample data from ws3:
<colgroup><col width="64" span="17" style="width:48pt"> </colgroup><tbody>
</tbody>
I am trying to check to see if two cells in Sheets("CHECKING") or (ws2), correspond with values in Sheets("Category Hierarchy (5)") or ws3. In row 1 of ws3 there are "Departments" and below that department from row 2 onwards are categories and these categories are unique values to only one department.
The value in Cell.offset(0,-2) is the category and the value in cell.offset(0,-3) is the department. We need to make sure that the category in ws2 in the column of the matched department in ws3. i.e. make sure there is not a mismatched category to a department.
The following code works fine but it takes a long time to loop through all the cells in rng1.
Is there a quicker way to increase speed? See below for code and samples of data from both ws.
Code:
Sub test()
Dim rng, catrng As Range
Dim ws As Worksheet
Set ws2 = Sheets("CHECKING")
Set rng1 = Range(ws2.Cells(1, 6), ws2.Cells(1, 6).End(xlDown))
For Each cell In rng1
Set ws3 = Sheets("CATEGORY HIERARCHY (" & cell.Offset(0, -1).Value & ")")
If Not cell.Offset(0, 2) = 0 Or IsError(Application.Match(cell.Offset(0, -3).Value, ws3.Rows("1:1"), 0)) Then
ws2.Cells(i, 8) = "0"
Else
x = Application.WorksheetFunction.Match(cell.Offset(0, -3), ws3.Rows("1:1"))
Set catrng = Range(ws3.Cells(2, x), ws3.Cells(1000, x))
If IsError(Application.VLookup(cell.Offset(0, -2), catrng, 1, False)) Then
cell.Offset(0, 4) = "1"
Else
cell.Offset(0, 4) = "0"
End If
End If
Next cell
End Sub
Here is some sample data from ws2:
Icy Hot | Private Label | Health & Beauty | First Aid Essential Oils/Lotions | 5 | 548 | Icy HotPrivate LabelHealth & BeautyFirst Aid Essential Oils/Lotions5 | 0 | 0 | 0 |
Signature Home | Private Label | Household Products | Toilet Cleaners | 5 | 577 | Signature HomePrivate LabelHousehold ProductsToilet Cleaners5 | 0 | 0 | 0 |
Signature Home | Private Label | General Merchandise | Household Gloves | 5 | 577 | Signature HomePrivate LabelGeneral MerchandiseHousehold Gloves5 | 0 | 0 | 0 |
Crest | Procter & Gamble | Health & Beauty | Toothbrush - Pwr | 5 | 578 | CrestProcter & GambleHealth & BeautyToothbrush - Pwr5 | 0 | 0 | 0 |
Soleil | Other Mfr | Health & Beauty | Razors & Blades | 5 | 602 | SoleilOther MfrHealth & BeautyRazors & Blades5 | 0 | 0 | 0 |
Irish Spring | Other Mfr | Health & Beauty | Liquid Soap | 5 | 609 | Irish SpringOther MfrHealth & BeautyLiquid Soap5 | 0 | 0 | 0 |
Ponds | Unilever | Health & Beauty | Facial Tissues | 5 | 613 | PondsUnileverHealth & BeautyFacial Tissues5 | 0 | 0 | 0 |
Just For Men | Other Mfr | Health & Beauty | Colourants | 5 | 626 | Just For MenOther MfrHealth & BeautyColourants5 | 0 | 0 | 0 |
Biotene | GlaxoSmithKline | Health & Beauty | Toothpaste | 5 | 662 | BioteneGlaxoSmithKlineHealth & BeautyToothpaste5 | 0 | 0 | 0 |
Dentu-Crème | GlaxoSmithKline | Health & Beauty | Denture Care | 5 | 662 | Dentu-CrèmeGlaxoSmithKlineHealth & BeautyDenture Care5 | 0 | 0 | 0 |
Love Beauty & Planet | Unilever | Health & Beauty | Cream / Lotion | 5 | 707 | Love Beauty & PlanetUnileverHealth & BeautyCream / Lotion5 | 0 | 0 | 0 |
Motrin | Other Mfr | Health & Beauty | Analgesics | 5 | 711 | MotrinOther MfrHealth & BeautyAnalgesics5 | 0 | 0 | 0 |
Bengay | Other Mfr | Health & Beauty | Medicinal | 5 | 711 | BengayOther MfrHealth & BeautyMedicinal5 | 0 | 0 | 0 |
One A Day | Bayer | Health & Beauty | Digestive Remedies | 5 | 716 | One A DayBayerHealth & BeautyDigestive Remedies5 | 0 | 0 | 0 |
Dove | Other Mfr | Health & Beauty | Shamp, Cond & Treat | 5 | 735 | DoveOther MfrHealth & BeautyShamp, Cond & Treat5 | 0 | 0 | 0 |
Ajax | Colgate Palmolive | Household Products | Dishwashing - Manual | 5 | 779 | AjaxColgate PalmoliveHousehold ProductsDishwashing - Manual5 | 0 | 0 | 0 |
Suavitel | Colgate Palmolive | Household Products | Fabric Care | 5 | 779 | SuavitelColgate PalmoliveHousehold ProductsFabric Care5 | 0 | 0 | 0 |
Fabuloso | Colgate Palmolive | Household Products | Fabric Care | 5 | 779 | FabulosoColgate PalmoliveHousehold ProductsFabric Care5 | 0 | 0 | 0 |
Murphys | Other Mfr | Health & Beauty | Liquid Soap | 5 | 779 | MurphysOther MfrHealth & BeautyLiquid Soap5 | 0 | 0 | 0 |
Tylenol | Johnson & Johnson | Health & Beauty | Medicinal | 5 | 809 | TylenolJohnson & JohnsonHealth & BeautyMedicinal5 | 0 | 0 | 0 |
Simply Sleep | Other Mfr | Health & Beauty | Vitamins | 5 | 813 | Simply SleepOther MfrHealth & BeautyVitamins5 | 0 | 0 | 0 |
Nature Bounty | Other Mfr | Health & Beauty | Vitamins | 5 | 844 | Nature BountyOther MfrHealth & BeautyVitamins5 | 0 | 1 | 0 |
Top Care | Other Mfr | Health & Beauty | Digestive Remedies | 5 | 896 | Top CareOther MfrHealth & BeautyDigestive Remedies5 | 0 | 0 | 0 |
Duo | Other Mfr | Health & Beauty | Cosmetics | 5 | 902 | DuoOther MfrHealth & BeautyCosmetics5 | 0 | 0 | 0 |
OGX | Other Mfr | Health & Beauty | Liquid Soap | 5 | 906 | OGXOther MfrHealth & BeautyLiquid Soap5 | 0 | 0 | 0 |
Up & Up | Private Label | Health & Beauty | Facial Tissues | 5 | 930 | Up & UpPrivate LabelHealth & BeautyFacial Tissues5 | 0 | 0 | 0 |
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>
Here the sample data from ws3:
Departments | Baby Needs | Bakery | Beverages | Chilled Food | Dry Grocery | Frozen Products | Fruit & Vegetables | General Merchandise | Health & Beauty | Household Products | Liquor | Meat Fresh | Newsagency / Tobacco | Petcare | Retailer Content | Snacking |
Categories | Baby Accessories | Bakery | Coffee Substitutes | Chilled Bread | Asian Foods | Fish Fingers | Fruit & Vegetables | Appliances | Accessories | Air Fresheners | Liquor | Meat Fresh | Flowers | Birds | Retailer Content | Confect Bars |
Baby Food & Formula | Cordial | Chilled Cheese | Baked Beans/Spaghetti | Frozen Chk/Turk/Duck | Audio | Analgesics | Bleach | Magazines | Chilled Petfood | Confect Bitesize | ||||||
Baby Needs | Home Brewing | Chilled Dips | Bottled Sauces | Frozen Finger Foods | Bags | Antiseptics | Dishwashing - Auto | Newspapers | Dog Treats | Confect Gifting | ||||||
Nappies | Hot Beverages | Chilled Fish | Cake Needs | Frozen Savouries | Bakeware | At Home Whitening | Dishwashing - Manual | Litter | Confect Gum | |||||||
Milk Modifiers | Chilled Noodles & Sauce | Canned Fruit | Frozen Snacks | Books | Bar Soap | Disinfectant | Other Pets | Confect Pre Teen | ||||||||
Powdered Milk / Coffee Whiteners | Deli Dips | Canned Meals | Frozen Pizza - Premium | Buckets & Bins | Colourants | Fabric Care | Pet Accessories | Confect Refresh | ||||||||
Tea | Deli Fresh Meals | Canned Meat | Frozen Fish | Camping | Condoms/Lubricants | Garbage Bags | Pet Health | Confect Seasonal | ||||||||
Ambient Juice | Deli Salad | Cereal - Adult | Frozen Desserts | Clothing | Cosmetics | Household Cleaners | Pet Toys | Confect Sharepacks | ||||||||
Coffee | Eggs | Chutney Pickles Relish | Frozen Meals | Crockery | Cotton Products | Household Cleaning - Brushware | Catfood | Nutritional Bars | ||||||||
CSD | Entertaining Antipasto/Cond | Cones Wafers Cups | Frozen Pastry | DVDs | Dental Floss | Insecticides | Dogfood | Nutritious Snacks | ||||||||
Energy Drinks | Fresh Cream | Cooking Chocolate | Frozen Potato | Electrical Fittings | Denture Care | Laundry Det - Liq | Nuts | |||||||||
Lifestyle Drinks | Fresh Custard | Cooking Nuts | Frozen Veg | Electricals | Deodorants | Laundry Det - Pwd | Shelf Stable Dips | |||||||||
Long Life Beverages | Fresh Pasta & Sauce | Dried Fruit | Ice Cream | Film | Depilatories | Paper Towels | Biscuits | |||||||||
Mineral Water | Smallgoods | Dry Pasta | Frozen Pizza | Fitness Equipment | Digestive Remedies | Prewash | Salty Snacks | |||||||||
Sports Drinks | Chilled Juice | Ethnic Gourmet Foods | Garden Care | Facial Tissues | Plastic Bags/Wraps/Foils | Chocolate Blocks | ||||||||||
Still Water | Fresh Milk | Hampers | Gift Cards | Feminine Hygiene | Sponges Scourers & Wipes | Candy Bags | ||||||||||
Yellow Spreads | Health Foods | Gifts | First Aid Essential Oils/Lotions | Toilet Cleaners | ||||||||||||
Yoghurt / Dessert | Healthfoods Diet Sport Products | Glassware | First Aid Footcare | Toilet Tissues | ||||||||||||
Herbs & Spices | Home Hardware | Fragrances | Fire Needs | |||||||||||||
Hot Packs | Homewares | Gift Packs | ||||||||||||||
Indian Foods | Hosiery | Hair Care - Styling | ||||||||||||||
Jelly | Household Gloves | Liquid Soap | ||||||||||||||
Mexican Food | *******ware | Medicinal | ||||||||||||||
Noodles - Cooking | Laundry Needs | Mens Aftershaves/Cologne | ||||||||||||||
Noodles - Snack | Light Globes | Mens Hair Care | ||||||||||||||
Pasta Cheese | Manchester | Mens Skin Care | ||||||||||||||
Pasta Sauces | Motoring | Mouth Wash | ||||||||||||||
Pasta Sauces - Ingredients | Outdoor | Nail Care | ||||||||||||||
Pickled Vegetables | Party and Picnic Needs | Pregnancy Kits | ||||||||||||||
Picnicware | Party Wrap and Foil | Shamp, Cond & Treat | ||||||||||||||
Recipe Bases | Phones | Shaving Preparations | ||||||||||||||
Rice | Seasonal Decorations | Shower Gel | ||||||||||||||
Salt | Stationery | Sun Care | ||||||||||||||
Sauces And Gravy | Toys | Talc | ||||||||||||||
Shelf Stable Dessert | Containers | Toothbrush - Man | ||||||||||||||
Side Dishes | Batteries | Toothbrush - Pwr | ||||||||||||||
Simmer Sauces | Toothpaste | |||||||||||||||
Sugar | Travel Toiletries | |||||||||||||||
Sugar Substitutes | Nicotine | |||||||||||||||
Toppings | Antifungal | |||||||||||||||
Vinegar | Anthelmintics | |||||||||||||||
Water Ices | Optical | |||||||||||||||
Wet Mustards | Hair Loss | |||||||||||||||
Baking Mixes | Lice Treatment | |||||||||||||||
Can Fish | Adult Incontinence | |||||||||||||||
Can Veg | Cream / Lotion | |||||||||||||||
Breakfast Cereal | Razors & Blades | |||||||||||||||
Cooking Oil | Upper Respiratory Tract | |||||||||||||||
Flour / Bread Mixes | Vitamins | |||||||||||||||
Mayo & Dressing | ||||||||||||||||
Spreads | ||||||||||||||||
Soup | ||||||||||||||||
Stock |
<colgroup><col width="64" span="17" style="width:48pt"> </colgroup><tbody>
</tbody>
Last edited by a moderator: