Set Worksheet as Variable in For Loop Error

WildBird

New Member
Joined
Jun 7, 2022
Messages
17
Office Version
  1. 365
Platform
  1. MacOS
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
ABCKLZAAAB
2Batch #BrandBrew DateRack DateCost per BatchCost per ozIngredient Inventory Updated
394Citra Smash3/16/224/23/22$563.30$ 0.015Yes
495Cucumber Gose3/22/224/25/22$919.99$ 0.028Yes
596Hibiscus Hoale3/25/224/26/22$1,249.17$ 0.036Yes
697Becker's Best3/29/225/11/22$1,261.65$ 0.020Yes
798Aviator4/5/225/2/22$529.80$ 0.045Yes
899Divine Apparition5/2/22Yes
9100Centurion4/7/224/28/22$650.60$ 0.018Yes
10101Mossy Rock4/8/224/28/22$1,271.05$ 0.036Yes
11102Becker's Best4/12/226/21/22$841.65$ 0.011Yes
12103Cucumber Gose5/4/225/25/22$920.07$ 0.024Yes
13104Aviator5/9/225/23/22$519.31$ 0.016Yes
14105Becker's Best5/11/22Yes
15106Peaches-n-Cream5/13/225/31/22Yes
16107Hop Train5/20/225/31/22$685.17$ 0.017Yes
17108Honey Kolsh5/24/226/8/22$652.87$ 0.017Yes
18109Becker's Best5/27/22Yes
19110Kiwi Cucumber Gose5/31/226/24/22$743.59$ 0.019Yes
20111Injector6/7/227/13/22$1,425.29$ 0.052Yes
21112Golden Promises6/9/227/7/22$898.63Yes
22113Lavendar Wheat 6/17/22$541.55$ 0.014Yes
23114Centurion6/24/227/7/22$662.58$ 0.017Yes
24115Cucumber Gose6/29/227/12/22$1,079.27$ 0.030Yes
25116Becker's Best7/6/22$1,280.79Yes
26117Aviator7/8/22$527.55Yes
27118Citra Smash7/15/22$779.48
28119Derailment7/19/22$580.93
Batch Data
Cells with Data Validation
CellAllowCriteria
B3:B28List='ORB Brands'!$A$2:$A$100
K3:K1048576List='Drop Downs'!$C$2:$C$6


Cell Formulas
RangeFormula
H3H3=15-2
H5H5=10-1
C3:C13C3='Ingredients & Pricing'!C2
C14:C35C14='Ingredients & Pricing'!C14
G3:G35G3=D3*E3+F3
J3:J35J3=G3+(H3*D3)-I3



ORB Inventory and Cost Analysis.xlsm
BCDEFGHIJKLMNOPQRST
1Citra Smash
2
3MaltHopsYeastMisc.
4ProductRecipe UnitAmountCostProductRecipe UnitAmountCostProductRecipe UnitAmountCostProductRecipe UnitAmountCost
5Rahr Standard 2-RowPrice / lb275190.85Hallertau Magnum - Green Products HopsPrice / lb2.5$24.98Fermentis SafAle™ US-05 - 500 gPrice / g50063.5Lactic Acid 88% - 4 kgPrice / mL4503.06
6Simpsons Finest Golden Promise™Price / lb197189.514Citra 2019Price / lb1$17.50BSG Brewers’ Gypsum (Calcium Sulfate) - 50 lbPrice / g630.0882
7Dingemans Cara 20Price / lb3231.104Citra 2019Price / lb2$35.00BSG Brewers’ Calcium Chloride - 50 lbPrice / g150.051
8Weyermann® Acidulated MaltPrice / lb1517.31Citra 2019Price / lb235BSG Brewers’ Gypsum (Calcium Sulfate) - 50 lbPrice / g190.0266
9Weyermann® CARAFOAM®Price / lb1514.73Citra 2019Price / lb235BSG Brewers’ Calcium Chloride - 50 lbPrice / g810.2754
10Citra 2019Price / lb352.5Whirlfloc® G - 5 lbPrice / g250.75
11Citra 2019Price / lb352.5Yeastex® 82 - 5 lbPrice / g400.804
12Fermcap® AT - 4 kgPrice / mL406.888
13Biofine® Clear - 25 kgPrice / mL5008.05
Citra Smash
Cell Formulas
RangeFormula
S5S5=250+200
Cells with Data Validation
CellAllowCriteria
G5:G13List='Ingredients & Pricing'!$K$2:$K$100
B1List='ORB Brands'!$A$2:$A$100
L5:L13List='Ingredients & Pricing'!$S$2:$S$99
Q5:Q13List='Ingredients & Pricing'!$AA$2:$AA$99
B5:B13List='Ingredients & Pricing'!$C$2:$C$100
 

Attachments

  • 10DF40C9-36AA-4645-B16D-35032167D612_4_5005_c.jpeg
    10DF40C9-36AA-4645-B16D-35032167D612_4_5005_c.jpeg
    23.5 KB · Views: 6
  • BBDE968D-AD65-4289-A1C0-EA9A6A870E02.jpeg
    BBDE968D-AD65-4289-A1C0-EA9A6A870E02.jpeg
    75.8 KB · Views: 6
  • 560231E1-2A3E-4C7E-814A-72776915123F.jpeg
    560231E1-2A3E-4C7E-814A-72776915123F.jpeg
    116 KB · Views: 5
  • 98CF90D0-284A-4DDD-B1C8-33976D0659E7.jpeg
    98CF90D0-284A-4DDD-B1C8-33976D0659E7.jpeg
    77.9 KB · Views: 5
  • 1DF065A5-681D-4FF7-A4A1-389083B37A7F.jpeg
    1DF065A5-681D-4FF7-A4A1-389083B37A7F.jpeg
    201.8 KB · Views: 5
  • 96E9786C-1D04-444C-8E72-7E7901A7B8F8.jpeg
    96E9786C-1D04-444C-8E72-7E7901A7B8F8.jpeg
    185.4 KB · Views: 6
  • 1CDF733B-3CA6-4B9D-B105-68C38C5DDB21.jpeg
    1CDF733B-3CA6-4B9D-B105-68C38C5DDB21.jpeg
    155.2 KB · Views: 6
That is a hint that you will need to use 'Set' in addition to the variable name.

Example:
VBA Code:
            Dim Brand As String
            Brand = Range("B" & i).Value

and:
VBA Code:
            Dim Recipe As Worksheet
            Set Recipe = Worksheets(Brand)
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Next step is to make your code Beautiful to look at. :)

VBA Code:
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.
 
Upvote 0
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:
VBA Code:
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
 
Upvote 0
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:
VBA Code:
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 :(
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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?
VBA Code:
Dim Recipe              As Worksheet
 

Attachments

  • D8DA7DEB-7198-414F-8454-FFAD93FE8ACA_4_5005_c.jpeg
    D8DA7DEB-7198-414F-8454-FFAD93FE8ACA_4_5005_c.jpeg
    14.3 KB · Views: 4
Upvote 0
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 ;)
 
Upvote 0
VBA Code:
Dim Recipe              As Worksheet
I also tried this, with no luck
VBA Code:
            Brand = Range("B" & i).Value                                                        '       Save cell value into Brand
            Set Recipe = Sheets(Brand)                                                      '       Save sheet name to Recipe••••ˇˇˇˇ
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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