For Each and If loops

Skunny11 · Jan 6, 2014

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)) >...

Snakehips · Jan 6, 2014

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 · Jan 6, 2014

Snakehips said:
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 · Jan 7, 2014

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

Skunny11 · Jan 7, 2014

jsotola said:

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

For Each and If loops

Skunny11

New Member

Snakehips

Well-known Member

Skunny11

New Member

jsotola

Well-known Member

Skunny11

New Member

Similar threads

Share this page

For Each and If loops

Skunny11

New Member

Snakehips

Well-known Member

Skunny11

New Member

jsotola

Well-known Member

Skunny11

New Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock