For Next: Subscript out of Range

Question

The code is for a userform, the user having three options, one being less than 70,000 square feet, one being between 70,000 and 150,000 square feet and one greater than 150,000 square feet. Based on what the user selects the code is then supposed to tally all buildings within the above set requirements and print it onto an Excel spreadsheet.

I get the subscript out of range error.

Option Explicit

Private Sub CommandButton1_Click()
'OK button

Dim Oshawa_Square_Feet_R        As Range
Dim Oshawa_Electricity_R        As Range
Dim Oshawa_Natural_Gas_R        As Range
Dim Oshawa_Size                 As Integer

Workbooks("Energy Consumption of Different Buildings").Activate
Worksheets("DurhamRegionSchools").Activate

Set Oshawa_Square_Feet_R = Workbooks("Energy Consumption of Different Buildings").Sheets("DurhamRegionSchools").Range("Oshawa_Square_Feet")

Set Oshawa_Electricity_R = Workbooks("Energy Consumption of Different Buildings").Sheets("DurhamRegionSchools").Range("Oshawa_Electricity")

Set Oshawa_Natural_Gas_R = Workbooks("Energy Consumption of Different Buildings").Sheets("DurhamRegionSchools").Range("Oshawa_Natural_Gas")

Oshawa_Size = Workbooks("Energy Consumption of Different Buildings").Sheets("DurhamRegionSchools").Range("Oshawa_Square_Feet").Count
'Oshawa_Size = Oshawa_Square_Feet.Count

Dim Net_Durham_SquareFeet       As Double 'For square feet of schools in durham that are less than 70,000 square feet, there was supposed to be more than one city. 

Dim Net_Durham_NaturalGas       As Double 'Natural Gas of the schools that are less than 70,000 sqaure feet. 
Dim Net_Durham_Electricity      As Double 'Electricity of the schools that are less than 70,000 sqaure feet. 

Dim NNet_Durham_SquareFeet      As Double 'For square feet of schools in durham that are greater than 70,000 but less than 150,000 square feet square feet, there was supposed to be more than one city. 

Dim NNet_Durham_NaturalGas      As Double 'Natural Gas of the schools that are greater than 70,000 and less than 150,000 sqaure feet. 

Dim NNet_Durham_Electricity     As Double 'Electricity of the schools that are greater than 70,000 and less than 150,000 sqaure feet. 

Dim NNNet_Durham_SquareFeet     As Double 'For square feet of schools in durham that are greater than 150,000 square feet square feet, there was supposed to be more than one city.

Dim NNNet_Durham_NaturalGas     As Double 'Natural Gas of the schools that are greater than 150,000 sqaure feet. 

Dim NNNet_Durham_Electricity    As Double 'Electricity of the schools that are greater than 150,000 sqaure feet. 

Dim c_Oshawa    As Double 'Square feet less than 70,000
Dim cc_Oshawa As Double 'Square feet between 70,000 and 150,000 square feet
Dim ccc_Oshawa As Double 'Square feet greater than 150,000 square feet 

Dim E_Oshawa As Double 'Electricity for schools less than 70,000
Dim EE_Oshawa As Double 'Between 70,000 and 150,000
Dim EEE_Oshawa As Double 'Greater than 150,000

Dim G_Oshawa As Double 'Natural Gas for schools less than 70,000
Dim GG_Oshawa As Double 'Natural Gas between 70,000 and 150,000
Dim GGG_Oshawa As Double 'Greater than 150,000

Dim i_O As Long

'Dim Oshawa_Y As Double ????

Dim Oshawa_Cell As Range

Dim c_FinalDisplay As Double
Dim E_FinalDisplay As Double
Dim G_FinalDIsplay As Double

For i_O = 1 To i_O = Oshawa_Size

    For Each Oshawa_Cell In Oshawa_Square_Feet_R
    Next Oshawa_Cell

    If (Oshawa_Cell < 70000) Then 'Should I use .Value          ?????

        c_Oshawa = c_Oshawa + Oshawa_Cell
        E_Oshawa = E_Oshawa + Oshawa_Electricity_R.Cells(i_O).Value
        G_Oshawa = G_Oshawa + Oshawa_Natural_Gas_R.Cells(i_O).Value

    End If

    If (Oshawa_Cell >= 70000 And Oshawa_Cell < 150000) Then

        cc_Oshawa = c_Oshawa + Oshawa_Cell
        EE_Oshawa = E_Oshawa + Oshawa_Electricity_R.Cells(i_O).Value
        GG_Oshawa = G_Oshawa + Oshawa_Natural_Gas_R.Cells(i_O).Value

    End If

    If (Oshawa_Cell >= 150000) Then

        ccc_Oshawa = c_Oshawa + Oshawa_Cell
        EEE_Oshawa = E_Oshawa + Oshawa_Electricity_R.Cells(i_O).Value
        GGG_Oshawa = G_Oshawa + Oshawa_Natural_Gas_R.Cells(i_O).Value

    End If

Next i_O

Net_Durham_SquareFeet = c_Oshawa 
Net_Durham_NaturalGas = E_Oshawa 
Net_Durham_Electricity = G_Oshawa 

NNet_Durham_SquareFeet = cc_Oshawa 
NNet_Durham_NaturalGas = EE_Oshawa 
NNet_Durham_Electricity = GG_Oshawa 

NNNet_Durham_SquareFeet = ccc_Oshawa 
NNNet_Durham_NaturalGas = GGG_Oshawa 
NNNet_Durham_Electricity = EEE_Oshawa 

If CheckBox1.Value = True Then

    c_FinalDisplay = c_FinalDisplay + Net_Durham_SquareFeet
    E_FinalDisplay = E_FinalDisplay + Net_Durham_Electricity
    G_FinalDIsplay = G_FinalDIsplay + Net_Durham_NaturalGas

Else

    c_FinalDisplay = c_FinalDisplay + 0
    E_FinalDisplay = E_FinalDisplay + 0
    G_FinalDIsplay = G_FinalDIsplay + 0

End If
If CheckBox2.Value = True Then

    c_FinalDisplay = c_FinalDisplay + NNet_Durham_SquareFeet
    E_FinalDisplay = E_FinalDisplay + NNet_Durham_Electricity
    G_FinalDIsplay = G_FinalDIsplay + NNet_Durham_NaturalGas

Else

    c_FinalDisplay = c_FinalDisplay + 0
    E_FinalDisplay = E_FinalDisplay + 0
    G_FinalDIsplay = G_FinalDIsplay + 0

End If

If CheckBox3.Value = True Then

    c_FinalDisplay = c_FinalDisplay + NNNet_Durham_SquareFeet
    E_FinalDisplay = E_FinalDisplay + NNNet_Durham_Electricity
    G_FinalDIsplay = G_FinalDIsplay + NNNet_Durham_NaturalGas

Else

    c_FinalDisplay = c_FinalDisplay + 0
    E_FinalDisplay = E_FinalDisplay + 0
    G_FinalDIsplay = G_FinalDIsplay + 0

End If

Worksheets("UserForm").Select
Range("B5").Value = squarefeet_FinalDisplay
Range("B6").Value = Electricity_FinalDisplay
Range("B7").Value = Gas_FinalDIsplay
MsgBox "The results are in cells B5 to B7"

End Sub

Given below are the ranges from the spreadsheet "DurhamRegionSchools" from the "Energy Of Different Buildings workbook."

Oshawa Sqaure Feet
43,577.15
30,159.92
31,424.13
50,822.88
25,926.48
41,146.66
43,348.42
59,230.57
29,318.73
166,639.23
143,257.95
39,719.34
55,486.34
12,672.88
57,654.72
29,578.68
37,022.98
22,144.05
22,556.85
32,656.61
57,445.91
17,160.89
173,126.74
54,228.58
163,490.87
133,326.62
57,832.30
64,650.00
130,685.16
39,977.16
56,147.78
45,572.20
37,147.33
33,475.22


Oshawa Electricity 
161,460.36
157,343.73
201,128.33
276,713.33
119,088.39
197,258.76
205,980.35
317,256.42
90,166.00
819,712.50
750,168.54
130,499.56
226,040.00
70,139.04
293,712.00
84,400.00
159,026.90
52,608.27
61,126.54
190,676.47
273,366.15
207,657.14
960,699.01
387,919.99
702,110.56
664,230.45
318,636.00
347,876.47
592,539.06
77,760.00
350,664.71
211,457.12
156,720.22
188,041.28


Oshawa Natural Gas
43,688.09
26,201.90
29,260.79
16,485.41
33,441.72
33,064.60
42,997.86
36,572.87
36,019.56
140,409.78
129,017.17
41,155.25
35,377.65
18,023.74
34,075.25
35,228.25
21,203.76
29,995.63
24,783.51
23,770.24
34,763.66
0.00
53,257.44
14,041.96
137,116.06
121,820.85
24,100.18
24,561.11
178,694.83
38,687.54
17,719.73
26,249.28
41,250.24
36,172.77

What line is causing the error? (And if this is your literal code, you have a typo on the "DIM EEE_Oshawa" line, due to a missing asterisk.) — DougM, Jul 16 '14 at 21:57
Worksheets("UserForm").Select near the end of the code. Also are there any other errors in my coding that the compiler might not be able to find? Im new to vba, i was supposed to use matlab but due to user friendliness chose this instead — Vajran Timothy Sarvendran, Jul 16 '14 at 22:02
DougM thanks I will correct that. It was supposed to be commented out. Yes this is my literal code, there were supposed to be six other cities but they are repetitive so I did not want to include it. Tim Williams, I have commented above which line causes that error. Thank you all a tonne for the help!! — Vajran Timothy Sarvendran, Jul 16 '14 at 22:08
Would I need to use arrays instead of ranges to be able to use a range of values from an excel spreadsheet within a for loop? One of my peers said I do, or would the way I did it be okay? — Vajran Timothy Sarvendran, Jul 16 '14 at 22:10
`Worksheets("UserForm").Select` if this is erroring with "subscript out of range" then that indicates there is no worksheet named "UserForm" in the active workbook. — Tim Williams, Jul 16 '14 at 22:23
Alright thanks, how would i check if i activated the correct workbook or which workbook is currently active? — Vajran Timothy Sarvendran, Jul 16 '14 at 22:35
so I did check if i called the correct worksheet and I have. It has no spelling errors, in fact theres a command button in that worksheet that opens the userform that runs the above code and it works as expected. Anything else that may be causing the error? — Vajran Timothy Sarvendran, Jul 16 '14 at 22:42
Your code should not be relying on activating/selecting any workbooks/worksheets to function correctly - there's rarely any need to do this, and it makes your code difficult to debug and maintain. See: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — Tim Williams, Jul 16 '14 at 22:59
I ran my code again and renamed the workseet userform to something else. However my output is all zeros although i have ranges of data for square feet electricity and natural gas for oshawa — Vajran Timothy Sarvendran, Jul 16 '14 at 23:09
I have edited my post to include those ranges, as you can see there are values for square feet, electricity and natural gas for schools in the city of oshawa but it doesnt want to add those values up regardless of what checkboxes i select. Would it be due to .select and or .activate? — Vajran Timothy Sarvendran, Jul 16 '14 at 23:17
The line `For i_O = 1 To i_O = Oshawa_Size` is wrong and the loop will never run (although the code will compile without error). It should be `For i_O = 1 To Oshawa_Size` but the loop body isn't going to work properly anyway (not sure what the nested loops are meant to achieve) — barrowc, Jul 16 '14 at 23:25
the nestef if statements are supposed to help essentially sort the schools in oshawa according to size. Oshawa_Cell was supposed to run through each value in the range Oshawa_Square_Feet_R and depending on the size the values are stored in c_Oshawa , cc_Oshawa or ccc_Oshawa. Also depending on the sizes the total electric and gas consumption for the schools that are either under 70,000 or between 70,000 and 150,000 or greater than 150,000 square feet are supposed to be tallied. barrowc, I was wondering why the loop body would not work? Any help is appreciated since im new and learning. — Vajran Timothy Sarvendran, Jul 17 '14 at 00:03

barrowc · Answer 1 · 2014-07-19T00:39:47.727

The loop For Each Oshawa_Cell In Oshawa_Square_Feet_R : Next Oshawa_Cell doesn't do anything because there aren't any statements between the For Each ... and Next ... parts. A working loop would look something like:

For Each Oshawa_Cell In Oshawa_Square_Feet_R
    If (Oshawa_Cell < 70000)

' do lots of stuff

    End If
Next Oshawa_Cell

As written, the For i_O = 1 To i_O = Oshawa_Size Next i_O loop is in the wrong place. Other than being semantically incorrect - it should say For i_O = 1 To Oshawa_Size, all this loop would do is run the inner For Each... loop as many times as there are cells in the Oshawa_Square_Feet range. The inner For Each loop already runs once for each cell in the range.

So, if the Oshawa_Square_Feet range contained 20 cells, the effect of both loops would be to run a total of 400 times and the figures you totalled for the various consumptions would be too high by a factor of 20.

Either type of loop would work - For ... Next with a loop counter or For Each ... Next over a range - but using both here makes no sense. You obviously want to reference the Oshawa_Electricity_R and Oshawa_Natural_Gas_R ranges with the appropriate offset so the normal For ... Next is probably easier to use, if the values are all in the same column.

I've assumed that your 2nd and 3rd If statements were also incorrect and changed cc_Oshawa = c_Oshawa + Oshawa_Cell.Value to cc_Oshawa = cc_Oshawa + Oshawa_Cell.Value etc. You were totalling the wrong variables:

For i_O = 1 To Oshawa_Size
    Set Oshawa_Cell = Oshawa_Square_Feet_R.Cells(i_o, 1)

    If (Oshawa_Cell.Value < 70000) Then
        c_Oshawa = c_Oshawa + Oshawa_Cell.Value
        E_Oshawa = E_Oshawa + Oshawa_Electricity_R.Cells(i_O, 1).Value
        G_Oshawa = G_Oshawa + Oshawa_Natural_Gas_R.Cells(i_O, 1).Value
    End If

    If (Oshawa_Cell.Value >= 70000 And Oshawa_Cell.Value < 150000) Then
        cc_Oshawa = cc_Oshawa + Oshawa_Cell.Value
        EE_Oshawa = EE_Oshawa + Oshawa_Electricity_R.Cells(i_O, 1).Value
        GG_Oshawa = GG_Oshawa + Oshawa_Natural_Gas_R.Cells(i_O, 1).Value
    End If

    If (Oshawa_Cell.Value >= 150000) Then
        ccc_Oshawa = ccc_Oshawa + Oshawa_Cell.Value
        EEE_Oshawa = EEE_Oshawa + Oshawa_Electricity_R.Cells(i_O, 1).Value
        GGG_Oshawa = GGG_Oshawa + Oshawa_Natural_Gas_R.Cells(i_O, 1).Value
    End If
Next i_O

If the values in each range aren't all in a single column then it's a bit more complicated but not impossible

Hey barrowc so i Editied my code as you have said above but now i get runtime error 91:object variable or with block variable not set. It highlights Oshawa_Cell = Oshawa_Square_Feet_R.Cells(i_O,1) — Vajran Timothy Sarvendran, Jul 18 '14 at 22:52
Sorry: you need to use `Set` when assigning an object so `Set Oshawa_Cell = Oshawa_Square_Feet_R.Cells(i_o, 1)` — barrowc, Jul 19 '14 at 00:39

For Next: Subscript out of Range

1 Answers1