Set Worksheet as Variable in For Loop Error

[WildBird]

I am trying to update an ingredient inventory with a few nested For loops and If statements and it worked for the first set which is Malt, but I started getting a Run-time error '9': Subscript out of range. Even when I removed the For loop for the hops it is still giving me the error even though it worked for the malts originally.

The function is supposed to loop through all entries in column AB with row index i to check if the batch has already been accounted for and if the cell is empty then set the variable Brand to the brand name in column B. Each brand has an associated worksheet with the same name as the brand that has the recipe, which lists the amount of each ingredient used.

For i = 3 To Batch_Entry

If Cells(i, 28).Value = "" Then

Dim Recipe As Worksheet
Dim Brand As String

Brand = Range("B" & i)
Set Recipe = Worksheets(Brand)

The error occurs at "Set Recipe = Worksheets(Brand)

When the If statement is true, it is supposed to enter a nested For loop that goes through all the malts used in the recipe and look through all the malts in the Ingredient Inventory worksheet to find the match and update the column with the amount used.

Once it has gone through the malts, it should do the same thing for the hops, yeast, and misc. ingredients used in the recipe, then mark the batch entry at row index i as "yes" and move to the next batch entry (I haven't entered that line of code yet. Waiting until I get the function working). I am pretty new to excel, so I'm sure this isn't the most efficient code to complete the desired task. Any help is appreciated, but hopefully it's an easy fix.

ORB Inventory and Cost Analysis.xlsm
	A	B	C								K	L														Z	AA	AB
2	Batch #	Brand	Brew Date									Rack Date														Cost per Batch	Cost per oz	Ingredient Inventory Updated
3	94	Citra Smash	3/16/22									4/23/22														$563.30	$ 0.015	Yes
4	95	Cucumber Gose	3/22/22									4/25/22														$919.99	$ 0.028	Yes
5	96	Hibiscus Hoale	3/25/22									4/26/22														$1,249.17	$ 0.036	Yes
6	97	Becker's Best	3/29/22									5/11/22														$1,261.65	$ 0.020	Yes
7	98	Aviator	4/5/22									5/2/22														$529.80	$ 0.045	Yes
8	99	Divine Apparition	5/2/22																									Yes
9	100	Centurion	4/7/22									4/28/22														$650.60	$ 0.018	Yes
10	101	Mossy Rock	4/8/22									4/28/22														$1,271.05	$ 0.036	Yes
11	102	Becker's Best	4/12/22									6/21/22														$841.65	$ 0.011	Yes
12	103	Cucumber Gose	5/4/22									5/25/22														$920.07	$ 0.024	Yes
13	104	Aviator	5/9/22									5/23/22														$519.31	$ 0.016	Yes
14	105	Becker's Best	5/11/22																									Yes
15	106	Peaches-n-Cream	5/13/22									5/31/22																Yes
16	107	Hop Train	5/20/22									5/31/22														$685.17	$ 0.017	Yes
17	108	Honey Kolsh	5/24/22									6/8/22														$652.87	$ 0.017	Yes
18	109	Becker's Best	5/27/22																									Yes
19	110	Kiwi Cucumber Gose	5/31/22									6/24/22														$743.59	$ 0.019	Yes
20	111	Injector	6/7/22									7/13/22														$1,425.29	$ 0.052	Yes
21	112	Golden Promises	6/9/22									7/7/22														$898.63		Yes
22	113	Lavendar Wheat	6/17/22																							$541.55	$ 0.014	Yes
23	114	Centurion	6/24/22									7/7/22														$662.58	$ 0.017	Yes
24	115	Cucumber Gose	6/29/22									7/12/22														$1,079.27	$ 0.030	Yes
25	116	Becker's Best	7/6/22																							$1,280.79		Yes
26	117	Aviator	7/8/22																							$527.55		Yes
27	118	Citra Smash	7/15/22																							$779.48
28	119	Derailment	7/19/22																							$580.93
Batch Data

Cells with Data Validation
Cell	Allow	Criteria
B3:B28	List	='ORB Brands'!$A$2:$A$100
K3:K1048576	List	='Drop Downs'!$C$2:$C$6

ORB Inventory and Cost Analysis.xlsm
	C	D	E	F	G	H	I	J
2	Product	lb / Bag	# of Full Bags	Additional lbs	Total lbs on Date of Inventory	Added Bags to Inventory	lb Used	Total lbs to Date
3	Rahr Standard 2-Row	55	1	47	102	13	440	377
4	Rahr Premium Pilsner	55	20	23.5	1123.5	15		1948.5
5	Rahr White Wheat	55	12	53	713	9		1208
6	William Crisp Crystal Light 45L	55	1	5	60			60
7	William Crisp Chocolate Malt	55	1	10	65			65
8	Crisp Naked Oat Malt	55	1	0	55			55
9	Crisp Finest Maris Otter®	55	0	0	0		95	-95
10	William Crisp Roasted Barley	55	0	53	53			53
11	Crisp Dark Munich Malt	55	0	42	42			42
12	Dingemans Cara 20	55	1	22	77		32	45
13	Dingemans Special B®	55	1	42	97			97
14	Gambrinus Honey Malt	55	0	31	31			31
15	Simpsons Finest Golden Promise™	55	4	0	220	3	417	-32
16	Simpsons Crystal Light	55	1	0	55			55
17	Simpsons DRC™	55	0	32	32			32
18	Simpsons Finest Pale Ale Maris Otter®	55	4	11	231			231
19	Simpsons Amber Malt	55	0	38	38			38
20	Simpsons Brown Malt	55	0	45	45			45
21	Weyermann® Acidulated Malt	55	0	16	16		15	1
22	Weyermann® CARAFOAM®	55	0	40	40	1	15	80
23	Weyermann® Brewing Malt (Type Cologne)	55	3	0	165			165
24	Weyermann® Pale Ale Malt	55	2	29	139			139
25	Weyermann® Vienna Malt	55	0	37	37			37
26	Weyermann® CARAMUNICH® Type 1	55	1	0	55			55
27	Weyermann® CARAMUNICH® Type 3	55	1	0	55			55
28	Weyermann® Munich Malt Type 1	55	2	0	110			110
29	Weyermann® Munich Malt Type 2	55	0	35	35			35
30	Weyermann® CARAFA® Type 2	55	0	35	35			35
31	Weyermann® Barke® Pilsner Malt	55	0	0	0			0
32	Grain Millers Flaked Barley - 50 lb	50	3	0	150			150
33	Grain Millers Flaked White Wheat - 50 lb	50	3	17	167		50	117
34	Grain Millers Flaked Oats - 50 lb	50	0	43	43	1	25	68
35	OIO Flaked Corn - 50 lb	50	0	0	0			0
Ingredient Inventory

Cell Formulas
Range	Formula
H3	H3	=15-2
H5	H5	=10-1
C3:C13	C3	='Ingredients & Pricing'!C2
C14:C35	C14	='Ingredients & Pricing'!C14
G3:G35	G3	=D3*E3+F3
J3:J35	J3	=G3+(H3*D3)-I3

ORB Inventory and Cost Analysis.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Citra Smash
2
3	Malt					Hops					Yeast					Misc.
4	Product	Recipe Unit	Amount	Cost		Product	Recipe Unit	Amount	Cost		Product	Recipe Unit	Amount	Cost		Product	Recipe Unit	Amount	Cost
5	Rahr Standard 2-Row	Price / lb	275	190.85		Hallertau Magnum - Green Products Hops	Price / lb	2.5	$24.98		Fermentis SafAle™ US-05 - 500 g	Price / g	500	63.5		Lactic Acid 88% - 4 kg	Price / mL	450	3.06
6	Simpsons Finest Golden Promise™	Price / lb	197	189.514		Citra 2019	Price / lb	1	$17.50							BSG Brewers’ Gypsum (Calcium Sulfate) - 50 lb	Price / g	63	0.0882
7	Dingemans Cara 20	Price / lb	32	31.104		Citra 2019	Price / lb	2	$35.00							BSG Brewers’ Calcium Chloride - 50 lb	Price / g	15	0.051
8	Weyermann® Acidulated Malt	Price / lb	15	17.31		Citra 2019	Price / lb	2	35							BSG Brewers’ Gypsum (Calcium Sulfate) - 50 lb	Price / g	19	0.0266
9	Weyermann® CARAFOAM®	Price / lb	15	14.73		Citra 2019	Price / lb	2	35							BSG Brewers’ Calcium Chloride - 50 lb	Price / g	81	0.2754
10						Citra 2019	Price / lb	3	52.5							Whirlfloc® G - 5 lb	Price / g	25	0.75
11						Citra 2019	Price / lb	3	52.5							Yeastex® 82 - 5 lb	Price / g	40	0.804
12																Fermcap® AT - 4 kg	Price / mL	40	6.888
13																Biofine® Clear - 25 kg	Price / mL	500	8.05
Citra Smash

Cell Formulas
Range	Formula
S5	S5	=250+200

Cells with Data Validation
Cell	Allow	Criteria
G5:G13	List	='Ingredients & Pricing'!$K$2:$K$100
B1	List	='ORB Brands'!$A$2:$A$100
L5:L13	List	='Ingredients & Pricing'!$S$2:$S$99
Q5:Q13	List	='Ingredients & Pricing'!$AA$2:$AA$99
B5:B13	List	='Ingredients & Pricing'!$C$2:$C$100

 

[johnnyL]

You said you tried the code that I commented. I just commented the existing code, I didn't change it. In other words if you were getting the error before I commented the code, it stands to reason that you would still get the error with the code that was commented. Make sense?

 

[WildBird]

You said you tried the code that I commented. I just commented the existing code, I didn't change it. In other words if you were getting the error before I commented the code, it stands to reason that you would still get the error with the code that was commented. Make sense?

Yes, that makes sense, but I have no idea what I need to modify to get rid of the error
Thank you for the extra commenting, I'll try and do better on commenting my codes in the future.

 

[johnnyL]

I would suggest reading through the commented code to see if it matches up to what your intentions are. Any questions on the comments that you don't understand, please ask for further explanation.

 

[WildBird]

I would suggest reading through the commented code to see if it matches up to what your intentions are. Any questions on the comments that you don't understand, please ask for further explanation.

The comments all made sense, but I don't see what I am missing. I added modifying the line " Brand = Worksheets("Batch Data").Range("B" & i).Value" but still no better.

Sub Update_Ingredient_Inventory_1()
'
    Dim i                   As Integer
    Dim j                   As Integer
    Dim k                   As Integer
    Dim h                   As Integer
    Dim m                   As Integer
    
    Dim Malt            As Integer
    Dim Hops            As Integer
    Dim Yeast            As Integer
    Dim Misc              As Integer
'
    Dim Batch_Entry     As Long
    Dim Brand               As String
    Dim Recipe               As Worksheet
'
    Dim Last_Malt          As String
    Dim Last_Hop            As String
    Dim Last_Yeast           As String
    Dim Last_Misc             As String
'
    Dim Last_Recipe_Malt   As String
    Dim Last_Recipe_Hop    As String
    Dim Last_Recipe_Yeast    As String
    Dim Last_Recipe_Misc      As String
     
'
    Batch_Entry = Worksheets("Batch Data").Range("B" & Rows.Count).End(xlUp).Row                ' Save last used row # of Column B to Batch_Entry
'
      Last_Malt = Worksheets("Ingredient Inventory").Range("C" & Rows.Count).End(xlUp).Row       ' Save last used row # of Column C to Last_Malt
       Last_Hop = Worksheets("Ingredient Inventory").Range("O" & Rows.Count).End(xlUp).Row       ' Save last used row # of Column O to Last_Hop
        Last_Yeast = Worksheets("Ingredient Inventory").Range("AA" & Rows.Count).End(xlUp).Row   ' Save last used row # of Column AA to Last_Yeast
         Last_Misc = Worksheets("Ingredient Inventory").Range("AM" & Rows.Count).End(xlUp).Row    ' Save last used row # of Column AM to Last_Misc

'
'Execute function for all entries in column B that have not already been updated
    For i = 3 To Batch_Entry                                                                    ' Loop through rows 3 to last row used column B of 'Batch Data' (Brand/Recipe Name)
        If Cells(i, 28).Value = "" Then                                                         '   If Column AB row i is blank then ... (Has not already been updated)
            Brand = Worksheets("Batch Data").Range("B" & i).Value               '       Save cell value (Brand name of batch entry in row i) as the variable Brand
            Set Recipe = Worksheets(Brand)                                                      '       Set Recipe as the sheet name of Brand
            Worksheets("Batch Data").Cells(i, 26).Value = Recipe.Cells(4, 1).Value              '       Save value from sheet Recipe A4 to 'Batch Data'
'                                                                                                                                   '               cell in column Z on row i ($Batch Cost$)
             Last_Recipe_Malt = Recipe.Range("B" & Rows.Count).End(xlUp).Row                    '       Save value from sheet Recipe Bx to Last_Recipe_Malt
              Last_Recipe_Hop = Recipe.Range("G" & Rows.Count).End(xlUp).Row                    '       Save value from sheet Recipe Gx to Last_Recipe_Hop
                Last_Recipe_Yeast = Recipe.Range("L" & Rows.Count).End(xlUp).Row                    '       Save value from sheet Recipe Lx to Last_Recipe_Yeast
                    Last_Recipe_Misc = Recipe.Range("Q" & Rows.Count).End(xlUp).Row                    '       Save value from sheet Recipe Qx to Last_Recipe_Misc

'
            For Malt = 5 To Last_Recipe_Malt                                                    '       Loop through rows 5 to last row used in Recipe Column B (recipe malt names)
                For j = 3 To Last_Malt                                                          '           Loop through rows 3 to last row used in
'                                                                                               '                   'Ingredient Inventory' column C (inventory malt names)
                    If Recipe.Cells(Malt, 2).Value = _
                            Worksheets("Ingredient Inventory").Cells(j, 3).Value Then           '               If Column B (col. index 2) of 'Recipe' = Column C (col. index 3) of
'                                                                                               '                       'Ingredient Inventory' then ...
                        Worksheets("Ingredient Inventory").Cells(j, 9).Value = _
                                Worksheets("Ingredient Inventory").Cells(j, 9).Value _
                                + Recipe.Cells(Malt, 4).Value                                   '                   Column I of 'Ingredient Inventory' (amount used) =
'                                                                                               '                           Column I of 'Ingredient Inventory'
'                                                                                               '                           value + 'Recipe' column D value
'                                                                                               '
                    End If
                Next j                                                                          '           Loop back through Inventory malts
            Next Malt                                                                           '       Loop back through Recipe malts
'
            For Hops = 5 To Last_Recipe_Hop                                                     '       Loop through rows 5 to last row used in Recipe Column G (recipe hop names)
                For k = 3 To Last_Hop                                                           '           Loop through rows 3 to last row used in 'Ingredient Inventory' column O (inventory hop names)
'                                                                                               '
                    If Recipe.Cells(Hops, 7).Value = _
                            Worksheets("Ingredient Inventory").Cells(k, 15).Value Then          '               If Column G (col. index 7) of 'Recipe' = Column O (col. index 15) of 'Ingredient Inventory'
'                                                                                               '                                               then ...
                        Worksheets("Ingredient Inventory").Cells(k, 21).Value = _
                                Worksheets("Ingredient Inventory").Cells(k, 21).Value _
                                + Recipe.Cells(Hops, 9).Value                                   '                   Save 'Ingredient Inventory' column U value + 'Recipe' column I value
'                                                                                                               '                    to 'Ingredient Inventory' column U (amount used, col. index 21)                                                                                             '
                    End If
                Next k                                                                          '       Loop back through Inventory Hops
            Next Hops                                                                           '   Loop back through Recipe Hops
'
            For Yeast = 5 To Last_Recipe_Yeast                                                    '       Loop through rows 5 to last row used in Recipe Column L (index 12)
                For h = 3 To Last_Yeast                                                          '           Loop through rows 3 to last row used in
'                                                                                               '                   'Ingredient Inventory' column AA (index 27)
                    If Recipe.Cells(Yeast, 12).Value = _
                            Worksheets("Ingredient Inventory").Cells(h, 27).Value Then           '               If Column L of 'Recipe' = Column AA of
'                                                                                               '                       '                        Ingredient Inventory' then ...
                        Worksheets("Ingredient Inventory").Cells(h, 33).Value = _
                                Worksheets("Ingredient Inventory").Cells(h, 33).Value _
                                + Recipe.Cells(Yeast, 14).Value                                   '                   Column AG (index 33) of 'Ingredient Inventory' =
'                                                                                               '                           Column AG of 'Ingredient Inventory'
'                                                                                               '                           value + 'Recipe' column N value (index 14)
'                                                                                               '
                    End If
                Next h                                                                          '           Loop back through Inventory Yeast
            Next Yeast                                                                           '       Loop back through Recipe Yeast
'
            For Misc = 5 To Last_Recipe_Misc                                                     '       Loop through rows 5 to last row used in Recipe Column Q (index 17)
                For m = 3 To Last_Misc                                                           '           Loop through rows 3 to last row used in
'                                                                                               '                   '     Ingredient Inventory' column AM (index 39)
                    If Recipe.Cells(Misc, 17).Value = _
                            Worksheets("Ingredient Inventory").Cells(m, 39).Value Then          '               If Column Q of 'Recipe' = Column AM of
'                                                                                               '                                '              Ingredient Inventory' then ...
                        Worksheets("Ingredient Inventory").Cells(m, 47).Value = _
                                Worksheets("Ingredient Inventory").Cells(m, 47).Value _
                                + Recipe.Cells(Misc, 19).Value                                   '                   Save 'Ingredient Inventory' column AU (index 47)
'                                                                                               '                           value + 'Recipe' column S (index 19) value
'                                                                                               '                           to 'Ingredient Inventory' column AU
'                                                                                               '
                    End If
                Next m                                                                          '       Loop back through Inventory Misc
            Next Misc                                                                           '   Loop back through Recipe Misc
        End If
    Next i                                                                                      ' Loop back through Batch Entries
End Sub

 

[johnnyL]

Without your actual file you are using, I can't step through the code to see where your issues are.

 

[WildBird]

Any one have an idea why "Dim Recipe As Worksheet" is not working correctly?

 

[WildBird]

Found the bug! I needed to specify which sheet to activate for the first If statement when checking if the cell was empty.