For Each and If loops

Skunny11

New Member
Joined
Jan 6, 2014
Messages
3
Hi everyone, below is the code I am using and for some reason every other time I change the numbers in rng5 and rng6 the loops will execute and run correctly but not every time (I change the number it runs correctly, i change the number again it doesnt run, i change it again and it runs correctly so on and so forth). I know its a problem with how I have my If statements or possibly for each loops set up but I think I am too close to the code and need an outside perspective. Maybe take a look at the "Set rng5/6..." statements in the If statements? Any help would be greatly appriciated, thanks in advance! - Matt

Code:
'Function attached to the Summary Sheet, Runs when a change is detected on the Summary Sheet.
Private Sub Worksheet_Calculate()
'   Sets Variables.
    Dim rng5 As Range
    Dim rng6 As Range
    Dim cell5 As Range
    Dim cell6 As Range
    Set rng5 = Range("F13")
    Set rng6 = Range("F114")
    
'   Stops the Excel Event Handlers from being called so that the function process and to prevent infinite loops.
    Application.EnableEvents = False
    
'   Production\Database\...
'   Begin a For Each loop that executes for each cell (cell.Value) in the Range (rng) given above in variables.
    For Each cell5 In rng5
        
'       If statement to check if cell.Value is greater or equal to 18000.
        If cell5.Value >= 18000 Then
'           If conditions are met then the given Row Ranges are unhid (SQL01 through SQL19 directories).
            Range("14:22").EntireRow.Hidden = False
            Range("23:111").EntireRow.Hidden = True
            Set rng5 = Range("F13:F22")
            
'               Sets the given Ranges for SQL01 through SQL19 directories equal to the default formula given to calculate the size of that directory.
'               Distributes the total size of the database evenly between all 10 directories SQL01 through SQL19.
                Range("G13:G22, H13:H22, I13:I22, J13:J22, K13:K22, L13:L22, M13:M22, N13:N22, O13:O22, P13:P22").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/10"
             
'               Clears contents of hidden cells.
                Range("G23:G111, H23:H111, I23:I111, J23:J111, K23:K111, L23:L111, M23:M111, N23:N111, O23:O111, P23:P111").ClearContents
                          
'       If statement to check if cell.Value is between 16000 and 18000.
        ElseIf cell5.Value >= 16000 And cell5.Value < 18000 Then
'           If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL09 directories).
            Range("14:21").EntireRow.Hidden = False
            Range("22:111").EntireRow.Hidden = True
            Set rng5 = Range("F13:F21")
            
'               Sets the given Ranges for SQL01 through SQL09 directories equal to the default formula given to calculate the size of that directory.
'               Distributes the total size of the database evenly between all 9 directories SQL01 through SQL09.
                Range("G13:G21, H13:H21, I13:I21, J13:J21, K13:K21, L13:L21, M13:M21, N13:N21, O13:O21, P13:P21").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/9"
             
'               Clears contents of hidden cells.
                Range("G22:G111, H22:H111, I22:I111, J22:J111, K22:K111, L22:L111, M22:M111, N22:N111, O22:O111, P22:P111").ClearContents
                             
'       If statement to check if cell.Value is between 14000 and 16000.
        ElseIf cell5.Value >= 14000 And cell5.Value < 16000 Then
'           If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL08 directories).
            Range("14:20").EntireRow.Hidden = False
            Range("21:111").EntireRow.Hidden = True
            Set rng5 = Range("F13:F20")
            
'               Sets the given Ranges for SQL01 through SQL08 directories equal to the default formula given to calculate the size of that directory.
'               Distributes the total size of the database evenly between all 8 directories SQL01 through SQL08.
                Range("G13:G20, H13:H20, I13:I20, J13:J20, K13:K20, L13:L20, M13:M20, N13:N20, O13:O20, P13:P20").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/8"
                 
'               Clears contents of hidden cells.
                Range("G21:G111, H21:H111, I21:I111, J21:J111, K21:K111, L21:L111, M21:M111, N21:N111, O21:O111, P21:P111").ClearContents
                                                  
'       If statement to check if cell.Value is between 12000 and 14000.
        ElseIf cell5.Value >= 12000 And cell5.Value < 14000 Then
'           If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL07 directories).
            Range("14:19").EntireRow.Hidden = False
            Range("20:111").EntireRow.Hidden = True
            Set rng5 = Range("F13:F19")
            
'               Sets the given Ranges for SQL01 through SQL07 directories equal to the default formula given to calculate the size of that directory.
'               Distributes the total size of the database evenly between all 7 directories SQL01 through SQL07.
                Range("G13:G19, H13:H19, I13:I19, J13:J19, K13:K19, L13:L19, M13:M19, N13:N19, O13:O19, P13:P19").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/7"
                                              
'               Clears contents of hidden cells.
                Range("G20:G111, H20:H111, I20:I111, J20:J111, K20:K111, L20:L111, M20:M111, N20:N111, O20:O111, P20:P111").ClearContents
                                                            
'       If statement to check if cell.Value is between 10000 and 12000.
        ElseIf cell5.Value >= 10000 And cell5.Value < 12000 Then
'           If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL06 directories).
            Range("14:18").EntireRow.Hidden = False
            Range("19:111").EntireRow.Hidden = True
            Set rng5 = Range("F13:F18")
            
'               Sets the given Ranges for SQL01 through SQL06 directories equal to the default formula given to calculate the size of that directory.
'               Distributes the total size of the database evenly between all 6 directories SQL01 through SQL06.
                Range("G13:G18, H13:H18, I13:I18, J13:J18, K13:K18, L13:L18, M13:M18, N13:N18, O13:O18, P13:P18").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/6"
               
'               Clears contents of hidden cells.
                Range("G19:G111, H19:H111, I19:I111, J19:J111, K19:K111, L19:L111, M19:M111, N19:N111, O19:O111, P19:P111").ClearContents
                                                           
'       If statement to check if cell.Value is between 8000 and 10000.
        ElseIf cell5.Value >= 8000 And cell5.Value < 10000 Then
'           If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL05 directories).
            Range("14:17").EntireRow.Hidden = False
            Range("18:111").EntireRow.Hidden = True
            Set rng5 = Range("F13:F17")
            
'               Sets the given Ranges for SQL01 through SQL05 directories equal to the default formula given to calculate the size of that directory.
'               Distributes the total size of the database evenly between all 5 directories SQL01 through SQL05.
                Range("G13:G17, H13:H17, I13:I17, J13:J17, K13:K17, L13:L17, M13:M17, N13:N17, O13:O17, P13:P17").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/5"
              
'               Clears contents of hidden cells.
                Range("G18:G111, H18:H111, I18:I111, J18:J111, K18:K111, L18:L111, M18:M111, N18:N111, O18:O111, P18:P111").ClearContents
                      
'       If statement to check if cell.Value is between 6000 and 8000.
        ElseIf cell5.Value >= 6000 And cell5.Value < 8000 Then
'           If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL04 directories).
            Range("14:16").EntireRow.Hidden = False
            Range("17:111").EntireRow.Hidden = True
            Set rng5 = Range("F13:F16")
            
'               Sets the given Ranges for SQL01 through SQL04 directories equal to the default formula given to calculate the size of that directory.
'               Distributes the total size of the database evenly between all 4 directories SQL01 through SQL04.
                Range("G13:G16, H13:H16, I13:I16, J13:J16, K13:K16, L13:L16, M13:M16, N13:N16, O13:O16, P13:P16").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/4"
                   
'               Clears contents of hidden cells.
                Range("G17:G111, H17:H111, I17:I111, J17:J111, K17:K111, L17:L111, M17:M111, N17:N111, O17:O111, P17:P111").ClearContents
                     
'       If statement to check if cell.Value is between 4000 and 6000.
        ElseIf cell5.Value >= 4000 And cell5.Value < 6000 Then
'           If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL03 directories).
            Range("14:15").EntireRow.Hidden = False
            Range("16:111").EntireRow.Hidden = True
            Set rng5 = Range("F13:F15")
            
'               Sets the given Ranges for SQL01 through SQL03 directories equal to the default formula given to calculate the size of that directory.
'               Distributes the total size of the database evenly between all 3 directories SQL01 through SQL03.
                Range("G13:G15, H13:H15, I13:I15, J13:J15, K13:K15, L13:L15, M13:M15, N13:N15, O13:O15, P13:P15").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/3"
                 
'               Clears contents of hidden cells.
                Range("G16:G111, H16:H111, I16:I111, J16:J111, K16:K111, L16:L111, M16:M111, N16:N111, O16:O111, P16:P111").ClearContents
                     
'       If statement to check if cell.Value is between 2000 and 4000.
        ElseIf cell5.Value >= 2000 And cell5.Value < 4000 Then
'           If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL02 directories).
            Range("14:14").EntireRow.Hidden = False
            Range("15:111").EntireRow.Hidden = True
            Set rng5 = Range("F13:F14")
            
'               Sets the given Ranges for SQL01 through SQL02 directories equal to the default formula given to calculate the size of that directory.
'               Distributes the total size of the database evenly between all 2 directories SQL01 through SQL02.
                Range("G13:G14, H13:H14, I13:I14, J13:J14, K13:K14, L13:L14, M13:M14, N13:N14, O13:O14, P13:P14").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/2"
          
'               Clears contents of hidden cells.
                Range("G15:G111, H15:H111, I15:I111, J15:J111, K15:K111, L15:L111, M15:M111, N15:N111, O15:O111, P15:P111").ClearContents
              
        Else
'           Hides the given Row Ranges on the Summary Sheet as default (SQL01 through SQL19).
            Range("14:111").EntireRow.Hidden = True
    
'           Sets the given Ranges for SQL01 through SQL19 directories equal to the default formula given to calculate the size of that directory.
            Range("G13:P13").Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))"
      
'           Clears contents of hidden cells.
            Range("G14:G111, H14:H111, I14:I111, J14:J111, K14:K111, L14:L111, M14:M111, N14:N111, O14:O111, P14:P111").ClearContents
              
        End If
    Next
    
'   Production\Backups\...
'   Begin a For Each loop that executes for each cell (cell.Value) in the Range (rng) given above in variables.
    For Each cell6 In rng6
        
'       If statement to check if cell.Value is greater or equal to 18000.
        If cell6.Value >= 18000 Then
'           If conditions are met then the given Row Ranges are unhid (SQL01 through SQL19 directories).
            Range("115:123").EntireRow.Hidden = False
            Range("124:212").EntireRow.Hidden = True
            Set rng6 = Range("F114:F123")
            
'               Sets the given Ranges SQL01 through SQL10 Backup directories equal to the default formula given to calculate the size of that directory.
                Range("G114:G123, H114:H123, I114:I123, J114:J123, K114:K123, L114:L123, M114:M123, N114:N123, O114:O123, P114:P123").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/10"
              
'               Clears contents of hidden cells.
                Range("G124:G212, H124:H212, I124:I212, J124:J212, K124:K212, L124:L212, M124:M212, N124:N212, O124:O212, P124:P212").ClearContents
                      
'       If statement to check if cell.Value is between 16000 and 18000.
        ElseIf cell6.Value >= 16000 And cell6.Value < 18000 Then
'           If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL09 directories).
            Range("115:122").EntireRow.Hidden = False
            Range("123:212").EntireRow.Hidden = True
            Set rng6 = Range("F114:F122")
            
'               Sets the given Ranges SQL01 through SQL09 Backup directories equal to the default formula given to calculate the size of that directory.
                Range("G114:G122, H114:H122, I114:I122, J114:J122, K114:K122, L114:L122, M114:M122, N114:N122, O114:O122, P114:P122").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/9"
                
'               Clears contents of hidden cells.
                Range("G123:G212, H123:H212, I123:I212, J123:J212, K123:K212, L123:L212, M123:M212, N123:N212, O123:O212, P123:P212").ClearContents
                     
'       If statement to check if cell.Value is between 14000 and 16000.
        ElseIf cell6.Value >= 14000 And cell6.Value < 16000 Then
'           If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL08 directories).
            Range("115:121").EntireRow.Hidden = False
            Range("122:212").EntireRow.Hidden = True
            Set rng6 = Range("F114:F121")
            
'               Sets the given Ranges SQL01 through SQL08 Backup directories equal to the default formula given to calculate the size of that directory.
                Range("G114:G121, H114:H121, I114:I121, J114:J121, K114:K121, L114:L121, M114:M121, N114:N121, O114:O121, P114:P121").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/8"
             
'               Clears contents of hidden cells.
                Range("G122:G212, H122:H212, I122:I212, J122:J212, K122:K212, L122:L212, M122:M212, N122:N212, O122:O212, P122:P212").ClearContents
                        
'       If statement to check if cell.Value is between 12300 and 14000.
        ElseIf cell6.Value >= 12000 And cell6.Value < 14000 Then
'           If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL07 directories).
            Range("115:120").EntireRow.Hidden = False
            Range("121:212").EntireRow.Hidden = True
            Set rng6 = Range("F114:F120")
            
'               Sets the given Ranges SQL01 through SQL07 Backup directories equal to the default formula given to calculate the size of that directory.
                Range("G114:G120, H114:H120, I114:I120, J114:J120, K114:K120, L114:L120, M114:M120, N114:N120, O114:O120, P114:P120").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/7"
              
'               Clears contents of hidden cells.
                Range("G121:G212, H121:H212, I121:I212, J121:J212, K121:K212, L121:L212, M121:M212, N121:N212, O121:O212, P121:P212").ClearContents
                       
'       If statement to check if cell.Value is between 10000 and 12300.
        ElseIf cell6.Value >= 10000 And cell6.Value < 12000 Then
'           If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL06 directories).
            Range("115:119").EntireRow.Hidden = False
            Range("120:212").EntireRow.Hidden = True
            Set rng6 = Range("F114:F119")
            
'               Sets the given Ranges SQL01 through SQL06 Backup directories equal to the default formula given to calculate the size of that directory.
                Range("G114:G119, H114:H119, I114:I119, J114:J119, K114:K119, L114:L119, M114:M119, N114:N119, O114:O119, P114:P119").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/6"
             
'               Clears contents of hidden cells.
                Range("G120:G212, H120:H212, I120:I212, J120:J212, K120:K212, L120:L212, M120:M212, N120:N212, O120:O212, P120:P212").ClearContents
                        
'       If statement to check if cell.Value is between 8000 and 10000.
        ElseIf cell6.Value >= 8000 And cell6.Value < 10000 Then
'           If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL05 directories).
            Range("115:118").EntireRow.Hidden = False
            Range("119:212").EntireRow.Hidden = True
            Set rng6 = Range("F114:F118")
            
'               Sets the given Ranges SQL01 through SQL05 Backup directories equal to the default formula given to calculate the size of that directory.
                Range("G114:G118, H114:H118, I114:I118, J114:J118, K114:K118, L114:L118, M114:M118, N114:N118, O114:O118, P114:P118").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/5"
             
'               Clears contents of hidden cells.
                Range("G119:G212, H119:H212, I119:I212, J119:J212, K119:K212, L119:L212, M119:M212, N119:N212, O119:O212, P119:P212").ClearContents
                        
'       If statement to check if cell.Value is between 6000 and 8000.
        ElseIf cell6.Value >= 6000 And cell6.Value < 8000 Then
'           If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL04 directories).
            Range("115:117").EntireRow.Hidden = False
            Range("118:212").EntireRow.Hidden = True
            Set rng6 = Range("F114:F117")
            
'               Sets the given Ranges SQL01 through SQL04 Backup directories equal to the default formula given to calculate the size of that directory.
                Range("G114:G117, H114:H117, I114:I117, J114:J117, K114:K117, L114:L117, M114:M117, N114:N117, O114:O117, P114:P117").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/4"
                 
'               Clears contents of hidden cells.
                Range("G118:G212, H118:H212, I118:I212, J118:J212, K118:K212, L118:L212, M118:M212, N118:N212, O118:O212, P118:P212").ClearContents
                  
'       If statement to check if cell.Value is between 4000 and 6000.
        ElseIf cell6.Value >= 4000 And cell6.Value < 6000 Then
'           If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL03 directories).
            Range("115:116").EntireRow.Hidden = False
            Range("117:212").EntireRow.Hidden = True
            Set rng6 = Range("F114:F116")
            
'               Sets the given Ranges SQL01 through SQL03 Backup directories equal to the default formula given to calculate the size of that directory.
                Range("G114:G116, H114:H116, I114:I116, J114:J116, K114:K116, L114:L116, M114:M116, N114:N116, O114:O116, P114:P116").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/3"
                
'               Clears contents of hidden cells.
                Range("G117:G212, H117:H212, I117:I212, J117:J212, K117:K212, L117:L212, M117:M212, N117:N212, O117:O212, P117:P212").ClearContents
                   
'       If statement to check if cell.Value is between 2000 and 4000.
        ElseIf cell6.Value >= 2000 And cell6.Value < 4000 Then
'           If conditions are met then the given Row Ranges are unhid while still concealing unneeded Rows (SQL01 Through SQL02 directories).
            Range("115:115").EntireRow.Hidden = False
            Range("116:212").EntireRow.Hidden = True
            Set rng6 = Range("F114:F115")
            
'               Sets the given Ranges SQL01 through SQL02 Backup directories equal to the default formula given to calculate the size of that directory.
                Range("G114:G115, H114:H115, I114:I115, J114:J115, K114:K115, L114:L115, M114:M115, N114:N115, O114:O115, P114:P115").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/2"
        
'               Clears contents of hidden cells.
                Range("G116:G212, H116:H212, I116:I212, J116:J212, K116:K212, L116:L212, M116:M212, N116:N212, O116:O212, P116:P212").ClearContents
               
        Else
'           Hides the given Row Ranges on the Summary Sheet as default (SQL01 through SQL19).
            Range("115:212").EntireRow.Hidden = True
            
'           Sets the given Ranges SQL01 through SQL19 Backup directories equal to the default formula given to calculate the size of that directory.
            Range("G114:P114").Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))"
        
'           Clears contents of hidden cells.
            Range("G115:G212, H115:H212, I115:I212, J115:J212, K115:K212, L115:L212, M115:M212, N115:N212, O115:O212, P115:P212").ClearContents
               
        End If
    Next
    
'   Terminates the loop back to the beginning of the function.
    Application.EnableEvents = True
    
End Sub

What the code is supposed to do is:
For each cell in the range (rng1/2)>
if cell value is between a certain numerical range >
then unhide/hide certain rows >
set the unhidden cell's formula to the given formula >
clear the contents of all the hidden cells >
set the range (rng1/2) = to the range of unhidden rows >
return to step 1 (for each cell in range (rng1/2)) >...
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,301
Office Version
  1. 2013
Platform
  1. Windows
Matt,

Welcome to MrExcel.

In what way is it working / not working?

In each loop you are starting with a single cell for rng5 and rng6. That sets up a loop of 1cell.
The re-set within each If statement e.g. Set rng5 = Range("F13:F22") is surely doing nothing???
 

Skunny11

New Member
Joined
Jan 6, 2014
Messages
3
Matt,

Welcome to MrExcel.

In what way is it working / not working?

In each loop you are starting with a single cell for rng5 and rng6. That sets up a loop of 1cell.
The re-set within each If statement e.g. Set rng5 = Range("F13:F22") is surely doing nothing???

Thanks for the reply, right now if i enter a number when i first open the worksheet then the loop works correctly (it unhides rows, adds the formulas to the unhidden rows, clears the data in the hidden rows) then when I change that number a second time the loop seems to break and it hides all the rows again (even when the number i enter should have some of those rows unhidden) then when I enter a number the third time the loop seems to work again. its really weird. Is there anything you can see logic wise that would cause problems when its doing the steps I outlined in the "this is what the code is supposed to do" section at the end of my original post?

I kind of get the feeling that the section in each If statement where i change the original range (Set rng5 = ...) is where I might be doing it wrong.
 

jsotola

Well-known Member
Joined
Nov 15, 2013
Messages
524
i reduced your code

i think that it works

select case statements do work same as your code

not sure if the "lookup" function works same way

Code:
[COLOR=#ff0000]'Function attached to the Summary Sheet, Runs when a change is detected on the Summary Sheet.[/COLOR]
Private Sub Worksheet_Calculate2()
[COLOR=#ff0000]'   Sets Variables.[/COLOR]
    Dim rng5 As Range
    Dim rng6 As Range
    
    
[COLOR=#ff0000]'    Dim cell5 As Range
'    Dim cell6 As Range
'    Set rng5 = Range("F13")
'    Set rng6 = Range("F114")[/COLOR]
    
    Dim levels(1 To 10) As Integer
    Dim indx(1 To 10) As Integer
    
    Dim i As Integer
    
    
    
    
    
[COLOR=#ff0000]'   Stops the Excel Event Handlers from being called so that the function process and to prevent infinite loops.[/COLOR]
    Application.EnableEvents = False
    
[COLOR=#ff0000]'   Production\Database\...
    
'    Select Case Range("F13")
'        Case Is >= 18000
'            i = 10
'        Case Is >= 16000, Is < 18000
'            i = 9
'        Case Is >= 14000, Is < 16000
'            i = 8
'        Case Is >= 12000, Is < 14000
'            i = 7
'        Case Is >= 10000, Is < 12000
'            i = 6
'        Case Is >= 8000, Is < 10000
'            i = 5
'        Case Is >= 6000, Is < 8000
'            i = 4
'        Case Is >= 4000, Is < 6000
'            i = 3
'        Case Is >= 2000, Is < 4000
'            i = 2
'        Case Else
'            i = 1
'        End Select
[/COLOR]



        i = Application.WorksheetFunction.Lookup(Range("F13").Value, [{0,2000,4000,6000,8000,10000,12000,14000,16000,18000}], [{1,2,3,4,5,6,7,8,9,10}])




        If i > 1 Then
            Set rng5 = Range("F13:F" & 12 + i)                 [COLOR=#ff0000] ' not sure what this does[/COLOR]
            Range("14:" & 12 + i).EntireRow.Hidden = False
        End If
            Range(13 + i & ":111").EntireRow.Hidden = True
            Range("G13:P" & 12 + i).Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/" & i
            Range("G" & 13 + i & ":P111").ClearContents




[COLOR=#ff0000]'   Production\Backups\...
'    Select Case Range("F114")
'        Case Is >= 18000
'            i = 10
'        Case Is >= 16000, Is < 18000
'            i = 9
'        Case Is >= 14000, Is < 16000
'            i = 8
'        Case Is >= 12000, Is < 14000
'            i = 7
'        Case Is >= 10000, Is < 12000
'            i = 6
'        Case Is >= 8000, Is < 10000
'            i = 5
'        Case Is >= 6000, Is < 8000
'            i = 4
'        Case Is >= 4000, Is < 6000
'            i = 3
'        Case Is >= 2000, Is < 4000
'            i = 2
'        Case Else
'            i = 1
'        End Select[/COLOR]
        
        i = Application.WorksheetFunction.Lookup(Range("F114").Value, [{0,2000,4000,6000,8000,10000,12000,14000,16000,18000}], [{1,2,3,4,5,6,7,8,9,10}])
        
        If i > 1 Then
            Set rng6 = Range("F114:F" & 113 + i)                 [COLOR=#ff0000] ' not sure what this does[/COLOR]
            Range("115:" & 113 + i).EntireRow.Hidden = False
        End If
        
        Range(114 + i & ":212").EntireRow.Hidden = True
        Range("G114:P" & 113 + i).Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/" & i
        Range("G1" & 14 + i & ":P212").ClearContents


[COLOR=#ff0000]'   Terminates the loop back to the beginning of the function.[/COLOR]
    Application.EnableEvents = True
    
End Sub
 
Last edited:

Skunny11

New Member
Joined
Jan 6, 2014
Messages
3
i reduced your code

i think that it works

select case statements do work same as your code

not sure if the "lookup" function works same way

Code:
[COLOR=#ff0000]'Function attached to the Summary Sheet, Runs when a change is detected on the Summary Sheet.[/COLOR]
Private Sub Worksheet_Calculate2()
[COLOR=#ff0000]'   Sets Variables.[/COLOR]
    Dim rng5 As Range
    Dim rng6 As Range
    
    
[COLOR=#ff0000]'    Dim cell5 As Range
'    Dim cell6 As Range
'    Set rng5 = Range("F13")
'    Set rng6 = Range("F114")[/COLOR]
    
    Dim levels(1 To 10) As Integer
    Dim indx(1 To 10) As Integer
    
    Dim i As Integer
    
    
    
    
    
[COLOR=#ff0000]'   Stops the Excel Event Handlers from being called so that the function process and to prevent infinite loops.[/COLOR]
    Application.EnableEvents = False
    
[COLOR=#ff0000]'   Production\Database\...
    
'    Select Case Range("F13")
'        Case Is >= 18000
'            i = 10
'        Case Is >= 16000, Is < 18000
'            i = 9
'        Case Is >= 14000, Is < 16000
'            i = 8
'        Case Is >= 12000, Is < 14000
'            i = 7
'        Case Is >= 10000, Is < 12000
'            i = 6
'        Case Is >= 8000, Is < 10000
'            i = 5
'        Case Is >= 6000, Is < 8000
'            i = 4
'        Case Is >= 4000, Is < 6000
'            i = 3
'        Case Is >= 2000, Is < 4000
'            i = 2
'        Case Else
'            i = 1
'        End Select
[/COLOR]



        i = Application.WorksheetFunction.Lookup(Range("F13").Value, [{0,2000,4000,6000,8000,10000,12000,14000,16000,18000}], [{1,2,3,4,5,6,7,8,9,10}])




        If i > 1 Then
            Set rng5 = Range("F13:F" & 12 + i)                 [COLOR=#ff0000]' not sure what this does[/COLOR]
            Range("14:" & 12 + i).EntireRow.Hidden = False
        End If
            Range(13 + i & ":111").EntireRow.Hidden = True
            Range("G13:P" & 12 + i).Formula = "=(CEILING(Worksheet!G$9*(1+Worksheet!G$10+Info!$B$14),5))/" & i
            Range("G" & 13 + i & ":P111").ClearContents




[COLOR=#ff0000]'   Production\Backups\...
'    Select Case Range("F114")
'        Case Is >= 18000
'            i = 10
'        Case Is >= 16000, Is < 18000
'            i = 9
'        Case Is >= 14000, Is < 16000
'            i = 8
'        Case Is >= 12000, Is < 14000
'            i = 7
'        Case Is >= 10000, Is < 12000
'            i = 6
'        Case Is >= 8000, Is < 10000
'            i = 5
'        Case Is >= 6000, Is < 8000
'            i = 4
'        Case Is >= 4000, Is < 6000
'            i = 3
'        Case Is >= 2000, Is < 4000
'            i = 2
'        Case Else
'            i = 1
'        End Select[/COLOR]
        
        i = Application.WorksheetFunction.Lookup(Range("F114").Value, [{0,2000,4000,6000,8000,10000,12000,14000,16000,18000}], [{1,2,3,4,5,6,7,8,9,10}])
        
        If i > 1 Then
            Set rng6 = Range("F114:F" & 113 + i)                 [COLOR=#ff0000]' not sure what this does[/COLOR]
            Range("115:" & 113 + i).EntireRow.Hidden = False
        End If
        
        Range(114 + i & ":212").EntireRow.Hidden = True
        Range("G114:P" & 113 + i).Formula = "=(CEILING(IF(Worksheet!G$11 = ""Yes"",SUM(G$13:G$112) * Info!$B$6,SUM(G$13:G$112)),5))/" & i
        Range("G1" & 14 + i & ":P212").ClearContents


[COLOR=#ff0000]'   Terminates the loop back to the beginning of the function.[/COLOR]
    Application.EnableEvents = True
    
End Sub

Hi Jsotola, thanks for the reply, that definitely looks much simpler than the lines for my code, thanks.

The If statements I have set up in my original code are set there to look at a cell (rng5 or rng6) and if that cell is under 2000 then only 1 row is shown with the value equal to what I have as that range. If it is between 2000 and 4000 then it adds a second row and then divides the value I have in that range between the two and then is supposed to set the range equal to the two rows now instead of just the one I originally had it as (this is where I am getting tripped up i think). between 4000 to 6000 it shows 3 rows and devides the value by 3. Basically its a spill over type of set up in increments of 2000 gigabytes.

Ill have to look deeper into the code you provided to try to get that to work but any more help would be greatly appriciated! thanks

-Matt
 
Last edited:

Forum statistics

Threads
1,137,302
Messages
5,680,710
Members
419,929
Latest member
Atlas Quinn

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
Top