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]

That is a hint that you will need to use 'Set' in addition to the variable name.

Example:

            Dim Brand As String
            Brand = Range("B" & i).Value

and:

            Dim Recipe As Worksheet
            Set Recipe = Worksheets(Brand)

 

[johnnyL]

Next step is to make your code Beautiful to look at.

Sub Update_Ingredient_Inventory()
'
    Dim Batch_Entry As Long
    Batch_Entry = Worksheets("Batch Data").Range("B" & Rows.Count).End(xlUp).Row
'
    Dim Last_Hop    As String
    Dim Last_Malt   As String
    Dim Last_Misc   As String
    Dim Last_Yeast  As String
'
      Last_Hop = Worksheets("Ingredient Inventory").Range("O" & Rows.Count).End(xlUp).Row
     Last_Malt = Worksheets("Ingredient Inventory").Range("C" & Rows.Count).End(xlUp).Row
     Last_Misc = Worksheets("Ingredient Inventory").Range("AM" & Rows.Count).End(xlUp).Row
    Last_Yeast = Worksheets("Ingredient Inventory").Range("AA" & 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_Hop     As String
            Dim Last_Recipe_Malt    As String
            Dim Last_Recipe_Yeast   As String
            Dim Last_Recipe_Misc    As String
'
              Last_Recipe_Hop = Recipe.Range("G" & Rows.Count).End(xlUp).Row
             Last_Recipe_Malt = Recipe.Range("B" & Rows.Count).End(xlUp).Row
             Last_Recipe_Misc = Recipe.Range("Q" & Rows.Count).End(xlUp).Row
            Last_Recipe_Yeast = Recipe.Range("L" & 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

Then you can get into fixing the code.

 

[johnnyL]

Ok I have done what I don't like doing, commenting code from other people.

The following is a brief commenting of the current code. See if it helps you figure out what to do next as far as the other loops you mentioned:

Sub Update_Ingredient_Inventory()
'
    Dim i                   As Integer
    Dim Batch_Entry         As Long
    Dim Brand               As String
    Dim Last_Hop            As String, Last_Malt            As String
    Dim Last_Misc           As String, Last_Yeast           As String
    Dim Last_Recipe_Hop     As String, Last_Recipe_Malt     As String
    Dim Last_Recipe_Misc    As String, Last_Recipe_Yeast    As String
    Dim Recipe              As Worksheet
'
    Batch_Entry = Worksheets("Batch Data").Range("B" & Rows.Count).End(xlUp).Row                ' Save last used row # of Column B to Batch_Entry
'
       Last_Hop = Worksheets("Ingredient Inventory").Range("O" & Rows.Count).End(xlUp).Row      ' Save last used row # of Column O to Last_Hop
      Last_Malt = Worksheets("Ingredient Inventory").Range("C" & Rows.Count).End(xlUp).Row      ' Save last used row # of Column C to Last_Malt
      Last_Misc = Worksheets("Ingredient Inventory").Range("AM" & Rows.Count).End(xlUp).Row     ' Save last used row # of Column AM to Last_Misc
     Last_Yeast = Worksheets("Ingredient Inventory").Range("AA" & Rows.Count).End(xlUp).Row     ' Save last used row # of Column AA to Last_Yeast
'
'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'
        If Cells(i, 28).Value = "" Then                                                         '   If Column AB cell is blank then ...
            Brand = Range("B" & i).Value                                                        '       Save cell value into Brand
            Set Recipe = Worksheets(Brand)                                                      '       Save sheet name to Recipe
            Worksheets("Batch Data").Cells(i, 26).Value = Recipe.Cells(4, 1).Value              '       Save value from sheet Recipe A4 to 'Batch Data'
'                                                                                               '               cell ABx
              Last_Recipe_Hop = Recipe.Range("G" & Rows.Count).End(xlUp).Row                    '       Save value from sheet Recipe Gx to Last_Recipe_Hop
             Last_Recipe_Malt = Recipe.Range("B" & Rows.Count).End(xlUp).Row                    '       Save value from sheet Recipe Bx to Last_Recipe_Malt
             Last_Recipe_Misc = Recipe.Range("Q" & Rows.Count).End(xlUp).Row                    '       Save value from sheet Recipe Qx to Last_Recipe_Misc
            Last_Recipe_Yeast = Recipe.Range("L" & Rows.Count).End(xlUp).Row                    '       Save value from sheet Recipe Lx to Last_Recipe_Yeast
'
            For Malt = 5 To Last_Recipe_Malt                                                    '       Loop through rows 5 to last row used in Recipe Column B
                For j = 3 To Last_Malt                                                          '           Loop through rows 3 to last row used in
'                                                                                               '                   'Ingredient Inventory' column C
                    If Recipe.Cells(Malt, 2).Value = _
                            Worksheets("Ingredient Inventory").Cells(j, 3).Value Then           '               If Column B of 'Recipe' = Column C 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' =
'                                                                                               '                           Column I of 'Ingredient Inventory'
'                                                                                               '                           value + 'Recipe' column D
'                                                                                               '                           value
                    End If
                Next j                                                                          '           Loop back
            Next Malt                                                                           '       Loop back
'
            For Hops = 5 To Last_Recipe_Hop                                                     '       Loop through rows 5 to last row used in Recipe Column G
                For k = 3 To Last_Hop                                                           '           Loop through rows 3 to last row used in
'                                                                                               '                   'Ingredient Inventory' column O
                    If Recipe.Cells(Hops, 7).Value = _
                            Worksheets("Ingredient Inventory").Cells(k, 15).Value Then          '               If Column G of 'Recipe' = Column O 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
                    End If
                Next k                                                                          '       Loop back
            Next Hops                                                                           '   Loop back
        End If
    Next i                                                                                      ' Loop back
End Sub

 

[WildBird]

Ok I have done what I don't like doing, commenting code from other people.

The following is a brief commenting of the current code. See if it helps you figure out what to do next as far as the other loops you mentioned:

Sub Update_Ingredient_Inventory()
'
    Dim i                   As Integer
    Dim Batch_Entry         As Long
    Dim Brand               As String
    Dim Last_Hop            As String, Last_Malt            As String
    Dim Last_Misc           As String, Last_Yeast           As String
    Dim Last_Recipe_Hop     As String, Last_Recipe_Malt     As String
    Dim Last_Recipe_Misc    As String, Last_Recipe_Yeast    As String
    Dim Recipe              As Worksheet
'
    Batch_Entry = Worksheets("Batch Data").Range("B" & Rows.Count).End(xlUp).Row                ' Save last used row # of Column B to Batch_Entry
'
       Last_Hop = Worksheets("Ingredient Inventory").Range("O" & Rows.Count).End(xlUp).Row      ' Save last used row # of Column O to Last_Hop
      Last_Malt = Worksheets("Ingredient Inventory").Range("C" & Rows.Count).End(xlUp).Row      ' Save last used row # of Column C to Last_Malt
      Last_Misc = Worksheets("Ingredient Inventory").Range("AM" & Rows.Count).End(xlUp).Row     ' Save last used row # of Column AM to Last_Misc
     Last_Yeast = Worksheets("Ingredient Inventory").Range("AA" & Rows.Count).End(xlUp).Row     ' Save last used row # of Column AA to Last_Yeast
'
'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'
        If Cells(i, 28).Value = "" Then                                                         '   If Column AB cell is blank then ...
            Brand = Range("B" & i).Value                                                        '       Save cell value into Brand
            Set Recipe = Worksheets(Brand)                                                      '       Save sheet name to Recipe
            Worksheets("Batch Data").Cells(i, 26).Value = Recipe.Cells(4, 1).Value              '       Save value from sheet Recipe A4 to 'Batch Data'
'                                                                                               '               cell ABx
              Last_Recipe_Hop = Recipe.Range("G" & Rows.Count).End(xlUp).Row                    '       Save value from sheet Recipe Gx to Last_Recipe_Hop
             Last_Recipe_Malt = Recipe.Range("B" & Rows.Count).End(xlUp).Row                    '       Save value from sheet Recipe Bx to Last_Recipe_Malt
             Last_Recipe_Misc = Recipe.Range("Q" & Rows.Count).End(xlUp).Row                    '       Save value from sheet Recipe Qx to Last_Recipe_Misc
            Last_Recipe_Yeast = Recipe.Range("L" & Rows.Count).End(xlUp).Row                    '       Save value from sheet Recipe Lx to Last_Recipe_Yeast
'
            For Malt = 5 To Last_Recipe_Malt                                                    '       Loop through rows 5 to last row used in Recipe Column B
                For j = 3 To Last_Malt                                                          '           Loop through rows 3 to last row used in
'                                                                                               '                   'Ingredient Inventory' column C
                    If Recipe.Cells(Malt, 2).Value = _
                            Worksheets("Ingredient Inventory").Cells(j, 3).Value Then           '               If Column B of 'Recipe' = Column C 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' =
'                                                                                               '                           Column I of 'Ingredient Inventory'
'                                                                                               '                           value + 'Recipe' column D
'                                                                                               '                           value
                    End If
                Next j                                                                          '           Loop back
            Next Malt                                                                           '       Loop back
'
            For Hops = 5 To Last_Recipe_Hop                                                     '       Loop through rows 5 to last row used in Recipe Column G
                For k = 3 To Last_Hop                                                           '           Loop through rows 3 to last row used in
'                                                                                               '                   'Ingredient Inventory' column O
                    If Recipe.Cells(Hops, 7).Value = _
                            Worksheets("Ingredient Inventory").Cells(k, 15).Value Then          '               If Column G of 'Recipe' = Column O 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
                    End If
                Next k                                                                          '       Loop back
            Next Hops                                                                           '   Loop back
        End If
    Next i                                                                                      ' Loop back
End Sub

I tried using this and I am still getting the same Run-Time '9': Subscript out of range error

 

[WildBird]

When I type Dim Recipe As Worksheet, Worksheet should appear blue like all the others that are defined but I can't get it to do that. I don't understand why is it still black text and not blue. Any suggestions?

 

[johnnyL]

I tried using this and I am still getting the same Run-Time '9': Subscript out of range error

I didn't change any of the code, why would you think you would get a different result?

 

[WildBird]

When I type Dim Recipe As Worksheet, Worksheet should appear blue like all the others that are defined but I can't get it to do that. I don't understand why is it still black text and not blue. Any suggestions?

Dim Recipe              As Worksheet

 

[johnnyL]

When I type Dim Recipe As Worksheet, Worksheet should appear blue like all the others that are defined but I can't get it to do that. I don't understand why is it still black text and not blue. Any suggestions?

See post #11

 

[WildBird]

Dim Recipe              As Worksheet

I also tried this, with no luck

            Brand = Range("B" & i).Value                                                        '       Save cell value into Brand
            Set Recipe = Sheets(Brand)                                                      '       Save sheet name to Recipe••••ˇˇˇˇ

 

[WildBird]

I didn't change any of the code, why would you think you would get a different result?

I guess I don't understand your hint and what change I need to make. I'm really new to using VBA and attempting to teach myself as I go. I thought I was using Set and the variable name, but am I not? Which variable are you referring to?