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]

Maybe change:

Brand = Range("B" & i)

to:

Brand = Range("B" & i).value

 

[WildBird]

Maybe change:

Brand = Range("B" & i)

to:

Brand = Range("B" & i).value

I made the change and it worked when I just had the For loop for the Malt, but I get the same error when I added back in the next For loop for the Hops. Any ideas?

 

[johnnyL]

My idea is to post your code so we can see what you are trying to do, and hopefully spot where your code is going off into the weeds so to speak.

Please remember, we can't see what you are trying to do unless you show us.

 

[WildBird]

Sub Update_Ingredient_Inventory()

Dim Batch_Entry As Long
Batch_Entry = Worksheets("Batch Data").Range("B" & Rows.Count).End(xlUp).Row

Dim Last_Malt As String
Dim Last_Hop As String
Dim Last_Yeast As String
Dim Last_Misc As String

Last_Malt = Worksheets("Ingredient Inventory").Range("C" & Rows.Count).End(xlUp).Row
Last_Hop = Worksheets("Ingredient Inventory").Range("O" & Rows.Count).End(xlUp).Row
Last_Yeast = Worksheets("Ingredient Inventory").Range("AA" & Rows.Count).End(xlUp).Row
Last_Misc = Worksheets("Ingredient Inventory").Range("AM" & Rows.Count).End(xlUp).Row

Dim i As Integer

'Execute function for all entries in column B that have not already been updated
For i = 3 To Batch_Entry

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

Dim Recipe As Worksheet
Dim Brand As String

Brand = Range("B" & i).Value
Set Recipe = Worksheets(Brand)
Worksheets("Batch Data").Cells(i, 26).Value = Recipe.Cells(4, 1).Value

Dim Last_Recipe_Malt As String
Dim Last_Recipe_Hop As String
Dim Last_Recipe_Yeast As String
Dim Last_Recipe_Misc As String

Last_Recipe_Malt = Recipe.Range("B" & Rows.Count).End(xlUp).Row
Last_Recipe_Hop = Recipe.Range("G" & Rows.Count).End(xlUp).Row
Last_Recipe_Yeast = Recipe.Range("L" & Rows.Count).End(xlUp).Row
Last_Recipe_Misc = Recipe.Range("Q" & Rows.Count).End(xlUp).Row

For Malt = 5 To Last_Recipe_Malt

For j = 3 To Last_Malt

If Recipe.Cells(Malt, 2).Value = Worksheets("Ingredient Inventory").Cells(j, 3).Value Then

Worksheets("Ingredient Inventory").Cells(j, 9).Value = Worksheets("Ingredient Inventory").Cells(j, 9).Value + Recipe.Cells(Malt, 4).Value

End If

Next j

Next Malt

For Hops = 5 To Last_Recipe_Hop

For k = 3 To Last_Hop

If Recipe.Cells(Hops, 7).Value = Worksheets("Ingredient Inventory").Cells(k, 15).Value Then

Worksheets("Ingredient Inventory").Cells(k, 21).Value = Worksheets("Ingredient Inventory").Cells(k, 21).Value + Recipe.Cells(Hops, 9).Value

End If

Next k

Next Hops

End If

Next i


End Sub

 

[WildBird]

My idea is to post your code so we can see what you are trying to do, and hopefully spot where your code is going off into the weeds so to speak.

Please remember, we can't see what you are trying to do unless you show us.

Just posted. It doesnt show the indentations, but I do have them in my code.

 

[johnnyL]

Just posted. It doesnt show the indentations, but I do have them in my code.

Use the code tags in the box where you make a post. When you want to insert code, click the VBA icon at the top of the posting window. It will show something like "CODE=vba][/CODE". In the middle of that is where you post the code. In between the middle '][' part.

 

[WildBird]

Sub Update_Ingredient_Inventory()

Dim Batch_Entry As Long
Batch_Entry = Worksheets("Batch Data").Range("B" & Rows.Count).End(xlUp).Row

Dim Last_Malt As String
Dim Last_Hop As String
Dim Last_Yeast As String
Dim Last_Misc As String

Last_Malt = Worksheets("Ingredient Inventory").Range("C" & Rows.Count).End(xlUp).Row
Last_Hop = Worksheets("Ingredient Inventory").Range("O" & Rows.Count).End(xlUp).Row
Last_Yeast = Worksheets("Ingredient Inventory").Range("AA" & Rows.Count).End(xlUp).Row
Last_Misc = Worksheets("Ingredient Inventory").Range("AM" & Rows.Count).End(xlUp).Row

Dim i As Integer

    'Execute function for all entries in column B that have not already been updated
    For i = 3 To Batch_Entry
    
        If Cells(i, 28).Value = "" Then
        
            Dim Recipe As Worksheet
            Dim Brand As String
            
            Brand = Range("B" & i).Value
            Set Recipe = Worksheets(Brand)
            Worksheets("Batch Data").Cells(i, 26).Value = Recipe.Cells(4, 1).Value
            
            Dim Last_Recipe_Malt As String
            Dim Last_Recipe_Hop As String
            Dim Last_Recipe_Yeast As String
            Dim Last_Recipe_Misc As String

            Last_Recipe_Malt = Recipe.Range("B" & Rows.Count).End(xlUp).Row
            Last_Recipe_Hop = Recipe.Range("G" & Rows.Count).End(xlUp).Row
            Last_Recipe_Yeast = Recipe.Range("L" & Rows.Count).End(xlUp).Row
            Last_Recipe_Misc = Recipe.Range("Q" & Rows.Count).End(xlUp).Row
            
            For Malt = 5 To Last_Recipe_Malt
                
                For j = 3 To Last_Malt
            
                    If Recipe.Cells(Malt, 2).Value = Worksheets("Ingredient Inventory").Cells(j, 3).Value Then
                    
                        Worksheets("Ingredient Inventory").Cells(j, 9).Value = Worksheets("Ingredient Inventory").Cells(j, 9).Value + Recipe.Cells(Malt, 4).Value
                    
                    End If
                    
                Next j
                
            Next Malt

            For Hops = 5 To Last_Recipe_Hop
                
                For k = 3 To Last_Hop
            
                    If Recipe.Cells(Hops, 7).Value = Worksheets("Ingredient Inventory").Cells(k, 15).Value Then
                    
                        Worksheets("Ingredient Inventory").Cells(k, 21).Value = Worksheets("Ingredient Inventory").Cells(k, 21).Value + Recipe.Cells(Hops, 9).Value
                    
                    End If
                    
                Next k
                
            Next Hops
            
        End If
        
    Next i


End Sub

 

[johnnyL]

Yeah... thats it!!!

 

[WildBird]

Sub Update_Ingredient_Inventory()

Dim Batch_Entry As Long
Batch_Entry = Worksheets("Batch Data").Range("B" & Rows.Count).End(xlUp).Row

Dim Last_Malt As String
Dim Last_Hop As String
Dim Last_Yeast As String
Dim Last_Misc As String

Last_Malt = Worksheets("Ingredient Inventory").Range("C" & Rows.Count).End(xlUp).Row
Last_Hop = Worksheets("Ingredient Inventory").Range("O" & Rows.Count).End(xlUp).Row
Last_Yeast = Worksheets("Ingredient Inventory").Range("AA" & Rows.Count).End(xlUp).Row
Last_Misc = Worksheets("Ingredient Inventory").Range("AM" & Rows.Count).End(xlUp).Row

Dim i As Integer

    'Execute function for all entries in column B that have not already been updated
    For i = 3 To Batch_Entry
   
        If Cells(i, 28).Value = "" Then
       
            Dim Recipe As Worksheet
            Dim Brand As String
           
            Brand = Range("B" & i).Value
            Set Recipe = Worksheets(Brand)
            Worksheets("Batch Data").Cells(i, 26).Value = Recipe.Cells(4, 1).Value
           
            Dim Last_Recipe_Malt As String
            Dim Last_Recipe_Hop As String
            Dim Last_Recipe_Yeast As String
            Dim Last_Recipe_Misc As String

            Last_Recipe_Malt = Recipe.Range("B" & Rows.Count).End(xlUp).Row
            Last_Recipe_Hop = Recipe.Range("G" & Rows.Count).End(xlUp).Row
            Last_Recipe_Yeast = Recipe.Range("L" & Rows.Count).End(xlUp).Row
            Last_Recipe_Misc = Recipe.Range("Q" & Rows.Count).End(xlUp).Row
           
            For Malt = 5 To Last_Recipe_Malt
               
                For j = 3 To Last_Malt
           
                    If Recipe.Cells(Malt, 2).Value = Worksheets("Ingredient Inventory").Cells(j, 3).Value Then
                   
                        Worksheets("Ingredient Inventory").Cells(j, 9).Value = Worksheets("Ingredient Inventory").Cells(j, 9).Value + Recipe.Cells(Malt, 4).Value
                   
                    End If
                   
                Next j
               
            Next Malt

            For Hops = 5 To Last_Recipe_Hop
               
                For k = 3 To Last_Hop
           
                    If Recipe.Cells(Hops, 7).Value = Worksheets("Ingredient Inventory").Cells(k, 15).Value Then
                   
                        Worksheets("Ingredient Inventory").Cells(k, 21).Value = Worksheets("Ingredient Inventory").Cells(k, 21).Value + Recipe.Cells(Hops, 9).Value
                   
                    End If
                   
                Next k
               
            Next Hops
           
        End If
       
    Next i


End Sub

I did notice that where I have "Dim Recipe As Worksheet" the word Worksheet isn't colored blue like the rest.