Speed Up Formula for +3000 rows

B

bayles

Board Regular
Joined
Oct 31, 2013
Messages
54
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.
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:
[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Icy Hot[/TD]
[TD="class: xl63, width: 64"]Private Label[/TD]
[TD="class: xl63, width: 64"]Health & Beauty[/TD]
[TD="class: xl63, width: 64"]First Aid Essential Oils/Lotions[/TD]
[TD="class: xl63, width: 64, align: right"]5[/TD]
[TD="class: xl63, width: 64, align: right"]548[/TD]
[TD="class: xl63, width: 64"]Icy HotPrivate LabelHealth & BeautyFirst Aid Essential Oils/Lotions5[/TD]
[TD="class: xl63, width: 64, align: right"]0[/TD]
[TD="class: xl63, width: 64, align: right"]0[/TD]
[TD="class: xl63, width: 64, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Signature Home[/TD]
[TD="class: xl63"]Private Label[/TD]
[TD="class: xl63"]Household Products[/TD]
[TD="class: xl63"]Toilet Cleaners[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]577[/TD]
[TD="class: xl63"]Signature HomePrivate LabelHousehold ProductsToilet Cleaners5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Signature Home[/TD]
[TD="class: xl63"]Private Label[/TD]
[TD="class: xl63"]General Merchandise[/TD]
[TD="class: xl63"]Household Gloves[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]577[/TD]
[TD="class: xl63"]Signature HomePrivate LabelGeneral MerchandiseHousehold Gloves5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Crest[/TD]
[TD="class: xl63"]Procter & Gamble[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Toothbrush - Pwr[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]578[/TD]
[TD="class: xl63"]CrestProcter & GambleHealth & BeautyToothbrush - Pwr5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Soleil[/TD]
[TD="class: xl63"]Other Mfr[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Razors & Blades[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]602[/TD]
[TD="class: xl63"]SoleilOther MfrHealth & BeautyRazors & Blades5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Irish Spring[/TD]
[TD="class: xl63"]Other Mfr[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Liquid Soap[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]609[/TD]
[TD="class: xl63"]Irish SpringOther MfrHealth & BeautyLiquid Soap5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Ponds[/TD]
[TD="class: xl63"]Unilever[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Facial Tissues[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]613[/TD]
[TD="class: xl63"]PondsUnileverHealth & BeautyFacial Tissues5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Just For Men[/TD]
[TD="class: xl63"]Other Mfr[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Colourants[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]626[/TD]
[TD="class: xl63"]Just For MenOther MfrHealth & BeautyColourants5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Biotene[/TD]
[TD="class: xl63"]GlaxoSmithKline[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Toothpaste[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]662[/TD]
[TD="class: xl63"]BioteneGlaxoSmithKlineHealth & BeautyToothpaste5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Dentu-Crème[/TD]
[TD="class: xl63"]GlaxoSmithKline[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Denture Care[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]662[/TD]
[TD="class: xl63"]Dentu-CrèmeGlaxoSmithKlineHealth & BeautyDenture Care5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Love Beauty & Planet[/TD]
[TD="class: xl63"]Unilever[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Cream / Lotion[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]707[/TD]
[TD="class: xl63"]Love Beauty & PlanetUnileverHealth & BeautyCream / Lotion5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Motrin[/TD]
[TD="class: xl63"]Other Mfr[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Analgesics[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]711[/TD]
[TD="class: xl63"]MotrinOther MfrHealth & BeautyAnalgesics5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Bengay[/TD]
[TD="class: xl63"]Other Mfr[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Medicinal[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]711[/TD]
[TD="class: xl63"]BengayOther MfrHealth & BeautyMedicinal5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]One A Day[/TD]
[TD="class: xl63"]Bayer[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Digestive Remedies[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]716[/TD]
[TD="class: xl63"]One A DayBayerHealth & BeautyDigestive Remedies5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Dove[/TD]
[TD="class: xl63"]Other Mfr[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Shamp, Cond & Treat[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]735[/TD]
[TD="class: xl63"]DoveOther MfrHealth & BeautyShamp, Cond & Treat5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Ajax[/TD]
[TD="class: xl63"]Colgate Palmolive[/TD]
[TD="class: xl63"]Household Products[/TD]
[TD="class: xl63"]Dishwashing - Manual[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]779[/TD]
[TD="class: xl63"]AjaxColgate PalmoliveHousehold ProductsDishwashing - Manual5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Suavitel[/TD]
[TD="class: xl63"]Colgate Palmolive[/TD]
[TD="class: xl63"]Household Products[/TD]
[TD="class: xl63"]Fabric Care[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]779[/TD]
[TD="class: xl63"]SuavitelColgate PalmoliveHousehold ProductsFabric Care5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Fabuloso[/TD]
[TD="class: xl63"]Colgate Palmolive[/TD]
[TD="class: xl63"]Household Products[/TD]
[TD="class: xl63"]Fabric Care[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]779[/TD]
[TD="class: xl63"]FabulosoColgate PalmoliveHousehold ProductsFabric Care5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Murphys[/TD]
[TD="class: xl63"]Other Mfr[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Liquid Soap[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]779[/TD]
[TD="class: xl63"]MurphysOther MfrHealth & BeautyLiquid Soap5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tylenol[/TD]
[TD="class: xl63"]Johnson & Johnson[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Medicinal[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]809[/TD]
[TD="class: xl63"]TylenolJohnson & JohnsonHealth & BeautyMedicinal5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Simply Sleep[/TD]
[TD="class: xl63"]Other Mfr[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Vitamins[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]813[/TD]
[TD="class: xl63"]Simply SleepOther MfrHealth & BeautyVitamins5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Nature Bounty[/TD]
[TD="class: xl63"]Other Mfr[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Vitamins[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]844[/TD]
[TD="class: xl63"]Nature BountyOther MfrHealth & BeautyVitamins5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Top Care[/TD]
[TD="class: xl63"]Other Mfr[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Digestive Remedies[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]896[/TD]
[TD="class: xl63"]Top CareOther MfrHealth & BeautyDigestive Remedies5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Duo[/TD]
[TD="class: xl63"]Other Mfr[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Cosmetics[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]902[/TD]
[TD="class: xl63"]DuoOther MfrHealth & BeautyCosmetics5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]OGX[/TD]
[TD="class: xl63"]Other Mfr[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Liquid Soap[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]906[/TD]
[TD="class: xl63"]OGXOther MfrHealth & BeautyLiquid Soap5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Up & Up[/TD]
[TD="class: xl63"]Private Label[/TD]
[TD="class: xl63"]Health & Beauty[/TD]
[TD="class: xl63"]Facial Tissues[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]930[/TD]
[TD="class: xl63"]Up & UpPrivate LabelHealth & BeautyFacial Tissues5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]




Here the sample data from ws3:
[TABLE="width: 1088"]
<colgroup><col width="64" span="17" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Departments[/TD]
[TD="class: xl65, width: 64"]Baby Needs[/TD]
[TD="class: xl65, width: 64"]Bakery[/TD]
[TD="class: xl65, width: 64"]Beverages[/TD]
[TD="class: xl65, width: 64"]Chilled Food[/TD]
[TD="class: xl65, width: 64"]Dry Grocery[/TD]
[TD="class: xl65, width: 64"]Frozen Products[/TD]
[TD="class: xl65, width: 64"]Fruit & Vegetables[/TD]
[TD="class: xl65, width: 64"]General Merchandise[/TD]
[TD="class: xl65, width: 64"]Health & Beauty[/TD]
[TD="class: xl65, width: 64"]Household Products[/TD]
[TD="class: xl65, width: 64"]Liquor[/TD]
[TD="class: xl65, width: 64"]Meat Fresh[/TD]
[TD="class: xl65, width: 64"]Newsagency / Tobacco[/TD]
[TD="class: xl65, width: 64"]Petcare[/TD]
[TD="class: xl65, width: 64"]Retailer Content[/TD]
[TD="class: xl65, width: 64"]Snacking[/TD]
[/TR]
[TR]
[TD="class: xl65"]Categories[/TD]
[TD]Baby Accessories[/TD]
[TD]Bakery[/TD]
[TD]Coffee Substitutes[/TD]
[TD]Chilled Bread[/TD]
[TD]Asian Foods[/TD]
[TD]Fish Fingers[/TD]
[TD]Fruit & Vegetables[/TD]
[TD]Appliances[/TD]
[TD]Accessories[/TD]
[TD]Air Fresheners[/TD]
[TD]Liquor[/TD]
[TD]Meat Fresh[/TD]
[TD]Flowers[/TD]
[TD]Birds[/TD]
[TD]Retailer Content[/TD]
[TD]Confect Bars[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Baby Food & Formula[/TD]
[TD]Cordial[/TD]
[TD]Chilled Cheese[/TD]
[TD]Baked Beans/Spaghetti[/TD]
[TD="colspan: 2"]Frozen Chk/Turk/Duck[/TD]
[TD]Audio[/TD]
[TD]Analgesics[/TD]
[TD]Bleach[/TD]
[TD][/TD]
[TD][/TD]
[TD]Magazines[/TD]
[TD="colspan: 2"]Chilled Petfood[/TD]
[TD]Confect Bitesize[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Baby Needs[/TD]
[TD]Home Brewing[/TD]
[TD]Chilled Dips[/TD]
[TD]Bottled Sauces[/TD]
[TD="colspan: 2"]Frozen Finger Foods[/TD]
[TD]Bags[/TD]
[TD]Antiseptics[/TD]
[TD="colspan: 2"]Dishwashing - Auto[/TD]
[TD][/TD]
[TD]Newspapers[/TD]
[TD="colspan: 2"]Dog Treats[/TD]
[TD]Confect Gifting[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Nappies[/TD]
[TD][/TD]
[TD]Hot Beverages[/TD]
[TD]Chilled Fish[/TD]
[TD]Cake Needs[/TD]
[TD="colspan: 2"]Frozen Savouries[/TD]
[TD]Bakeware[/TD]
[TD]At Home Whitening[/TD]
[TD="colspan: 3"]Dishwashing - Manual[/TD]
[TD][/TD]
[TD]Litter[/TD]
[TD][/TD]
[TD]Confect Gum[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Milk Modifiers[/TD]
[TD]Chilled Noodles & Sauce[/TD]
[TD]Canned Fruit[/TD]
[TD="colspan: 2"]Frozen Snacks[/TD]
[TD]Books[/TD]
[TD]Bar Soap[/TD]
[TD="colspan: 2"]Disinfectant[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Other Pets[/TD]
[TD]Confect Pre Teen[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Powdered Milk / Coffee Whiteners[/TD]
[TD]Deli Dips[/TD]
[TD]Canned Meals[/TD]
[TD="colspan: 2"]Frozen Pizza - Premium[/TD]
[TD]Buckets & Bins[/TD]
[TD]Colourants[/TD]
[TD="colspan: 2"]Fabric Care[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Pet Accessories[/TD]
[TD]Confect Refresh[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Tea[/TD]
[TD]Deli Fresh Meals[/TD]
[TD]Canned Meat[/TD]
[TD="colspan: 2"]Frozen Fish[/TD]
[TD]Camping[/TD]
[TD]Condoms/Lubricants[/TD]
[TD="colspan: 2"]Garbage Bags[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Pet Health[/TD]
[TD]Confect Seasonal[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Ambient Juice[/TD]
[TD]Deli Salad[/TD]
[TD]Cereal - Adult[/TD]
[TD="colspan: 2"]Frozen Desserts[/TD]
[TD]Clothing[/TD]
[TD]Cosmetics[/TD]
[TD="colspan: 3"]Household Cleaners[/TD]
[TD][/TD]
[TD]Pet Toys[/TD]
[TD][/TD]
[TD]Confect Sharepacks[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Coffee[/TD]
[TD]Eggs[/TD]
[TD]Chutney Pickles Relish[/TD]
[TD="colspan: 2"]Frozen Meals[/TD]
[TD]Crockery[/TD]
[TD]Cotton Products[/TD]
[TD="colspan: 4"]Household Cleaning - Brushware[/TD]
[TD]Catfood[/TD]
[TD][/TD]
[TD]Nutritional Bars[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CSD[/TD]
[TD]Entertaining Antipasto/Cond[/TD]
[TD]Cones Wafers Cups[/TD]
[TD="colspan: 2"]Frozen Pastry[/TD]
[TD]DVDs[/TD]
[TD]Dental Floss[/TD]
[TD="colspan: 2"]Insecticides[/TD]
[TD][/TD]
[TD][/TD]
[TD]Dogfood[/TD]
[TD][/TD]
[TD]Nutritious Snacks[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Energy Drinks[/TD]
[TD]Fresh Cream[/TD]
[TD]Cooking Chocolate[/TD]
[TD="colspan: 2"]Frozen Potato[/TD]
[TD]Electrical Fittings[/TD]
[TD]Denture Care[/TD]
[TD="colspan: 2"]Laundry Det - Liq[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Nuts[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lifestyle Drinks[/TD]
[TD]Fresh Custard[/TD]
[TD]Cooking Nuts[/TD]
[TD="colspan: 2"]Frozen Veg[/TD]
[TD]Electricals[/TD]
[TD]Deodorants[/TD]
[TD="colspan: 2"]Laundry Det - Pwd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shelf Stable Dips[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Long Life Beverages[/TD]
[TD]Fresh Pasta & Sauce[/TD]
[TD]Dried Fruit[/TD]
[TD="colspan: 2"]Ice Cream[/TD]
[TD]Film[/TD]
[TD]Depilatories[/TD]
[TD="colspan: 2"]Paper Towels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Biscuits[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mineral Water[/TD]
[TD]Smallgoods[/TD]
[TD]Dry Pasta[/TD]
[TD="colspan: 2"]Frozen Pizza[/TD]
[TD]Fitness Equipment[/TD]
[TD]Digestive Remedies[/TD]
[TD]Prewash[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Salty Snacks[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sports Drinks[/TD]
[TD]Chilled Juice[/TD]
[TD="colspan: 3"]Ethnic Gourmet Foods[/TD]
[TD]Garden Care[/TD]
[TD]Facial Tissues[/TD]
[TD="colspan: 3"]Plastic Bags/Wraps/Foils[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Chocolate Blocks[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Still Water[/TD]
[TD]Fresh Milk[/TD]
[TD]Hampers[/TD]
[TD][/TD]
[TD][/TD]
[TD]Gift Cards[/TD]
[TD]Feminine Hygiene[/TD]
[TD="colspan: 3"]Sponges Scourers & Wipes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Candy Bags[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yellow Spreads[/TD]
[TD="colspan: 2"]Health Foods[/TD]
[TD][/TD]
[TD]Gifts[/TD]
[TD]First Aid Essential Oils/Lotions[/TD]
[TD="colspan: 2"]Toilet Cleaners[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yoghurt / Dessert[/TD]
[TD="colspan: 3"]Healthfoods Diet Sport Products[/TD]
[TD]Glassware[/TD]
[TD]First Aid Footcare[/TD]
[TD="colspan: 2"]Toilet Tissues[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Herbs & Spices[/TD]
[TD][/TD]
[TD]Home Hardware[/TD]
[TD]Fragrances[/TD]
[TD="colspan: 2"]Fire Needs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Hot Packs[/TD]
[TD][/TD]
[TD][/TD]
[TD]Homewares[/TD]
[TD="colspan: 2"]Gift Packs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Indian Foods[/TD]
[TD][/TD]
[TD]Hosiery[/TD]
[TD="colspan: 2"]Hair Care - Styling[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jelly[/TD]
[TD][/TD]
[TD][/TD]
[TD]Household Gloves[/TD]
[TD="colspan: 2"]Liquid Soap[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Mexican Food[/TD]
[TD][/TD]
[TD]*******ware[/TD]
[TD="colspan: 2"]Medicinal[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Noodles - Cooking[/TD]
[TD][/TD]
[TD]Laundry Needs[/TD]
[TD="colspan: 3"]Mens Aftershaves/Cologne[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Noodles - Snack[/TD]
[TD][/TD]
[TD]Light Globes[/TD]
[TD="colspan: 2"]Mens Hair Care[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Pasta Cheese[/TD]
[TD][/TD]
[TD]Manchester[/TD]
[TD="colspan: 2"]Mens Skin Care[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Pasta Sauces[/TD]
[TD][/TD]
[TD]Motoring[/TD]
[TD="colspan: 2"]Mouth Wash[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Pasta Sauces - Ingredients[/TD]
[TD]Outdoor[/TD]
[TD]Nail Care[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Pickled Vegetables[/TD]
[TD][/TD]
[TD]Party and Picnic Needs[/TD]
[TD="colspan: 2"]Pregnancy Kits[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Picnicware[/TD]
[TD][/TD]
[TD]Party Wrap and Foil[/TD]
[TD="colspan: 3"]Shamp, Cond & Treat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Recipe Bases[/TD]
[TD][/TD]
[TD]Phones[/TD]
[TD="colspan: 3"]Shaving Preparations[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Rice[/TD]
[TD][/TD]
[TD][/TD]
[TD]Seasonal Decorations[/TD]
[TD="colspan: 2"]Shower Gel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Salt[/TD]
[TD][/TD]
[TD][/TD]
[TD]Stationery[/TD]
[TD]Sun Care[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Sauces And Gravy[/TD]
[TD][/TD]
[TD]Toys[/TD]
[TD]Talc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Shelf Stable Dessert[/TD]
[TD]Containers[/TD]
[TD="colspan: 2"]Toothbrush - Man[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Side Dishes[/TD]
[TD][/TD]
[TD]Batteries[/TD]
[TD="colspan: 2"]Toothbrush - Pwr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Simmer Sauces[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Toothpaste[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sugar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Travel Toiletries[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Sugar Substitutes[/TD]
[TD][/TD]
[TD][/TD]
[TD]Nicotine[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Toppings[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Antifungal[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Vinegar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Anthelmintics[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Water Ices[/TD]
[TD][/TD]
[TD][/TD]
[TD]Optical[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Wet Mustards[/TD]
[TD][/TD]
[TD][/TD]
[TD]Hair Loss[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Baking Mixes[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Lice Treatment[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Can Fish[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Adult Incontinence[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Can Veg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Cream / Lotion[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Breakfast Cereal[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Razors & Blades[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Cooking Oil[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Upper Respiratory Tract[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Flour / Bread Mixes[/TD]
[TD][/TD]
[TD][/TD]
[TD]Vitamins[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Mayo & Dressing[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Spreads[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Soup[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Stock[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited by a moderator:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Have you worked with arrays in VBA yet?

If not, that is what you should start researching.

Since excel 2007 and beyond, cycling through cells in a range has been extremely slow, so the best practice is to load a range of cells to an array, all at once. Do whatever work needs to be done within excel's memory without touching cells on a sheet. Then finally load back to the sheet the output all at once.

Below is a simple demo of how that might work.

Code: 
Sub arrExample()
'load array in and spit array out


arr = Range("a1:b3") 'load range into 2d array 'arr becomes a 2 dimensional array loaded with the range's values


'processing the range could happen here



Range("c4:d6") = arr  'export the 2d array back to a different range






End Sub
 
Last edited:
Upvote 0
Hi Odin,

I am completely self taught in VBA and I have been googling and looking at videos on how to accomplish this but it is all going a bit over my head.

Would you mind giving me some more detail in your answer and using the formulas I have above.

I would be very appreciative if you could.

Thanks
 
Upvote 0
How about
Code: 
Sub bayles()
   Dim CatAry As Variant
   Dim Dic As Object
   Dim r As Long, c As Long
   Dim Cl As Range
   
   Application.ScreenUpdating = False
   
   CatAry = Sheets("Category Hierarchy (5)").Range("A1").CurrentRegion.Value2
   Set Dic = CreateObject("scripting.dictionary")
   
   For c = 2 To UBound(CatAry, 2)
      Dic.Add CatAry(1, c), CreateObject("scripting.dictionary")
      For r = 2 To UBound(CatAry)
         Dic(CatAry(1, c))(CatAry(r, c)) = Empty
      Next r
   Next c
   
   With Sheets("Checking")
      For Each Cl In .Range("C2", .Range("C" & Rows.Count).End(xlUp))
         If Not Dic.Exists(Cl.Value) Then
            Cl.Offset(, 7).Value = 1
         ElseIf Not Dic(Cl.Value).Exists(Cl.Offset(, 1).Value) Then
            Cl.Offset(, 7).Value = 1
         Else
            Cl.Offset(, 7).Value = 0
         End If
      Next Cl
   End With
End Sub
 
Upvote 0
Thanks so much Fluff.

That was great. I'll try to learn from this code as to how to implement arrays. I can usually fluke my way through it eventually.

Cheers
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Vlookup in Array rather than looping
Replies
4
Views
805
Norie
Norie

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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