Hi all,
I am doing a vlookup on loop and it is taking a long time to cycle through 20,000 rows. I am new to arrays, dictionary etc so looking for some guidance on this one.
Much appreciated.
Here is the code that works fine albeit slow along with some sample data:
Sub test()
Dim rng As Range
Dim ws As Worksheet
Set ws = Sheets("BRANDS")
Set ws1 = Sheets("CHECKING")
Set rng = ws.Range(ws.Range("F2"), ws.Range("A1000000").End(xlUp).Offset(0, 5))
Set rng1 = Range(ws1.Range("F1"), ws1.Range("F1").End(xlDown))
rng.FormulaR1C1 = "=RC[-5]&RC[-2]"
rng.Value = rng.Value
For Each Cell In rng1
If Not Cell.Offset(0, 2) = 0 Or Not Cell.Offset(0, 3) = 0 Or Not Cell.Offset(0, 4) = 0 Then
Cell.Offset(0, 5) = "0"
Else
If IsError(Application.VLookup(Cell.Offset(0, -5) & Cell.Offset(0, -2), Worksheets("BRANDS").Range("F:F"), 1, False)) Then
Cell.Offset(0, 5) = "1"
Else
Cell.Offset(0, 5) = "0"
End If
End If
Next Cell
End Sub
ws sample data
<colgroup><col span="6"></colgroup><tbody>
</tbody>
Sample data for ws1
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>
I am doing a vlookup on loop and it is taking a long time to cycle through 20,000 rows. I am new to arrays, dictionary etc so looking for some guidance on this one.
Much appreciated.
Here is the code that works fine albeit slow along with some sample data:
Sub test()
Dim rng As Range
Dim ws As Worksheet
Set ws = Sheets("BRANDS")
Set ws1 = Sheets("CHECKING")
Set rng = ws.Range(ws.Range("F2"), ws.Range("A1000000").End(xlUp).Offset(0, 5))
Set rng1 = Range(ws1.Range("F1"), ws1.Range("F1").End(xlDown))
rng.FormulaR1C1 = "=RC[-5]&RC[-2]"
rng.Value = rng.Value
For Each Cell In rng1
If Not Cell.Offset(0, 2) = 0 Or Not Cell.Offset(0, 3) = 0 Or Not Cell.Offset(0, 4) = 0 Then
Cell.Offset(0, 5) = "0"
Else
If IsError(Application.VLookup(Cell.Offset(0, -5) & Cell.Offset(0, -2), Worksheets("BRANDS").Range("F:F"), 1, False)) Then
Cell.Offset(0, 5) = "1"
Else
Cell.Offset(0, 5) = "0"
End If
End If
Next Cell
End Sub
ws sample data
BRAND | MANUFACTURE | DEPARTMENT | CATEGORY | RANK IN BRAND |
0.05 | Other Mfr | Health & Beauty | Accessories | 0.05Health & Beauty1 |
0.05 | Other Mfr | General Merchandise | Appliances | 0.05General Merchandise1 |
1 Cal | Other Mfr | Beverages | Energy Drinks - On The Go | 1 CalBeverages1 |
10 Things I Hate About You | Other Mfr | General Merchandise | DVDs | 10 Things I Hate About YouGeneral Merchandise1 |
100 Pipers | Other Mfr | Liquor | Spirits, Mixers | 100 PipersLiquor1 |
1000 Hour | Other Mfr | Health & Beauty | Cosmetics - Eyes | 1000 HourHealth & Beauty1 |
1000 Hour | Other Mfr | Health & Beauty | Cosmetics - Nails | 1000 HourHealth & Beauty3 |
1000 Hour | Other Mfr | Health & Beauty | Colourants | 1000 HourHealth & Beauty2 |
1000 Hour | Other Mfr | Health & Beauty | First Aid Footcare | 1000 HourHealth & Beauty4 |
1000 Hour | Other Mfr | General Merchandise | Toys | 1000 HourGeneral Merchandise1 |
100Bon | Other Mfr | Health & Beauty | Fragrances | 100BonHealth & Beauty1 |
101 Dalmations | Other Mfr | General Merchandise | DVDs | 101 DalmationsGeneral Merchandise1 |
111skin | Other Mfr | Health & Beauty | Crm/Lotn - Face Other | 111skinHealth & Beauty1 |
12 Days of Christmas | Other Mfr | Snacking | Biscuit - Seasonal | 12 Days of ChristmasSnacking1 |
12 Days of Christmas | Other Mfr | Snacking | Confect Seasonal | 12 Days of ChristmasSnacking2 |
12 Days of Christmas | Other Mfr | General Merchandise | Gifts | 12 Days of ChristmasGeneral Merchandise1 |
17 Again | Other Mfr | General Merchandise | DVDs | 17 AgainGeneral Merchandise1 |
1800 | Other Mfr | Liquor | Spirits, Mixers | 1800Liquor2 |
19 Crimes | Other Mfr | Liquor | Wine | 19 CrimesLiquor2 |
1964 | Other Mfr | General Merchandise | Clothing | 1964General Merchandise1 |
1st Years | Other Mfr | Baby Needs | Baby Accessories | 1st YearsBaby Needs1 |
2 Guns | Other Mfr | General Merchandise | DVDs | 2 GunsGeneral Merchandise1 |
2012 | Other Mfr | General Merchandise | DVDs | 2012General Merchandise1 |
20th Century Fox | 20th Century Fox | General Merchandise | DVDs | 20th Century FoxGeneral Merchandise1 |
21 | Other Mfr | General Merchandise | DVDs | 21General Merchandise1 |
21 Jump Street | Other Mfr | General Merchandise | DVDs | 21 Jump StreetGeneral Merchandise1 |
22 Jump Street | Other Mfr | General Merchandise | DVDs | 22 Jump StreetGeneral Merchandise1 |
22cm Patterned PVC Playball | Other Mfr | General Merchandise | Toys | 22cm Patterned PVC PlayballGeneral Merchandise1 |
23rd Street Gin | Other Mfr | Liquor | Spirits, Mixers | 23rd Street GinLiquor1 |
24 Daily | Private Label | Health & Beauty | First Aid Footcare | 24 DailyHealth & Beauty1 |
24 Daily | Private Label | Health & Beauty | Mouth Wash | 24 DailyHealth & Beauty2 |
24 Daily | Private Label | Health & Beauty | Liquid Soap | 24 DailyHealth & Beauty3 |
24 Daily | Private Label | Health & Beauty | Crm/Lotn - Face Other | 24 DailyHealth & Beauty4 |
24 Daily | Private Label | Health & Beauty | Cotton Products | 24 DailyHealth & Beauty5 |
24 Daily | Private Label | Health & Beauty | Shamp, Cond & Treat | 24 DailyHealth & Beauty6 |
24 Daily | Private Label | Health & Beauty | Crm/Lotn - Hnd / Bdy | 24 DailyHealth & Beauty7 |
24 Daily | Private Label | Baby Needs | Wipes | 24 DailyBaby Needs1 |
24 Daily | Private Label | Baby Needs | Baby Accessories | 24 DailyBaby Needs2 |
24 Daily | Private Label | Health & Beauty | Shower Gel | 24 DailyHealth & Beauty8 |
24 Daily | Private Label | Health & Beauty | Toothbrush - Man | 24 DailyHealth & Beauty9 |
24 Daily | Private Label | Health & Beauty | Dental Floss | 24 DailyHealth & Beauty10 |
24 Daily | Private Label | Health & Beauty | Male B&R | 24 DailyHealth & Beauty11 |
24 Daily | Private Label | Health & Beauty | Crm/Lotn - Thpt Clns | 24 DailyHealth & Beauty12 |
24 Daily | Private Label | Health & Beauty | Toothbrush - Pwr | 24 DailyHealth & Beauty13 |
24 Daily | Private Label | Health & Beauty | Depilatories | 24 DailyHealth & Beauty14 |
24 Daily | Private Label | Health & Beauty | Toothpaste | 24 DailyHealth & Beauty15 |
24 Daily | Private Label | Health & Beauty | Female B&R | 24 DailyHealth & Beauty16 |
<colgroup><col span="6"></colgroup><tbody>
</tbody>
Sample data for ws1
Alka-Seltzer | Alka-Seltzer | Health & Beauty | Containers | 5 | 539 | Alka-SeltzerAlka-SeltzerHealth & BeautyContainers5 | 0 | 0 | 1 |
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 |
Just For Men | 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 |
Viviscal | Other Mfr. | Health & Beauty | Shamp, Cond & Treat | 5 | 953 | ViviscalOther Mfr.Health & BeautyShamp, Cond & Treat5 | 0 | 0 | 0 |
Viviscal | Other Mfr | Health & Beauty | Vitamins | 5 | 955 | ViviscalOther MfrHealth & BeautyVitamins5 | 0 | 0 | 0 |
23andMe | Other Mfr | Health & Beauty | Medicinal | 5 | 959 | 23andMeOther MfrHealth & BeautyMedicinal5 | 0 | 0 | 0 |
Lip Smacker | Other Mfr | Health & Beauty | Fabric Care | 5 | 979 | Lip SmackerOther MfrHealth & BeautyCosmetics5 | 0 | 0 | 0 |
Claratin | Bayer | Health & Beauty | Upper Respiratory Tract | 5 | 992 | ClaratinBayerHealth & BeautyUpper Respiratory Tract5 | 0 | 0 | 0 |
Bull Frog | Other Mfr | Health & Beauty | Sun Care | 5 | 1002 | Bull FrogOther MfrHealth & BeautySun Care5 | 0 | 0 | 0 |
Shoprite | Private Label | Health & Beauty | Toothbrush - Man | 5 | 1004 | ShopritePrivate LabelHealth & BeautyToothbrush - Man5 | 0 | 0 | 0 |
Every Man Jack | Other Mfr | Health & Beauty | Shower Gel | 5 | 1008 | Every Man JackOther MfrHealth & BeautyShower Gel5 | 0 | 0 | 0 |
MegaRed | Other Mfr | Health & Beauty | Vitamins | 5 | 1018 | MegaRedOther MfrHealth & BeautyVitamins5 | 0 | 0 | 0 |
Nivea | Other Mfr | Health & Beauty | Mens Skin Care | 5 | 1021 | NiveaOther MfrHealth & BeautyMens Skin Care5 | 0 | 0 | 0 |
No-Ad | Other Mfr | Health & Beauty | Sun Care | 5 | 1022 | No-AdOther MfrHealth & BeautySun Care5 | 0 | 0 | 0 |
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>