Need some help with stopping code from looping in IF statement after first IF is true...

[kbishop94]

I know its doing this (looping through repeatedly instead of stopping once the "IF" is true) because I do not have my "Next" and "End If" functions where they need to be, but I cannot figure out where they go. In this particular example there are 12 names (columns) that it finds, so its looping through the code 12 times.... I know this because I have a couple executions in there just to serve as a way of knowing exactly which "If" or "ElseIf" its executing (shading a commandbutton a certain color to represent that 'If' or 'ElseIf'), plus a message box popping up telling me which employees (columns) are being displayed. These are just temporary until I can get this code figured out.

Private Sub CommandButton28_Click()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
ActiveWindow.ScrollColumn = 1

Dim cP As Range
Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count

e = 0
r = 0

For Each cP In Range(Cells(4, 6), Cells(4, lCol)).SpecialCells(xlCellTypeVisible)

[B][COLOR=#008000]' Code for: if Row 4 contains only "R" 's in the cells[/COLOR][/B]
[B][COLOR=#008000]' ************************************************************************************************[/COLOR][/B]
If cP.Value = "R" Then

    CommandButton17.BackColor = 65280 [COLOR=#008000]' Bright green "R"[/COLOR]
    MsgBox "Rosenberg Employees"

r = 1

[B][COLOR=#008000]' Code for: if Row 4 contains only "E" 's in the cells[/COLOR][/B]
[B][COLOR=#008000]' ************************************************************************************************[/COLOR][/B]
ElseIf cP.Value = "E" Then

Dim i As Long
 
    For Each Cell In Range(Cells(5, 6), Cells(5, lCol))
    If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
    Next
    For i = 3 To ActiveSheet.UsedRange.Columns.Count Step 2
    Columns(i).Hidden = Not Columns(i).Hidden
    Next i
[B][COLOR=#008000]' THIS BIT OF CODE (ABOVE) IS TELLING IT TO BASICALLY "RESET" THE SPREADSHEET BY UN-HIDING ALL NAMES (COLUMNS) AND THEN HIDING THE COLUMN IMMEDIATELY ADJACENT TO EACH NAME COLUMN. [/COLOR][/B]

    CommandButton17.BackColor = 12648384[COLOR=#008000] ' light green "E"[/COLOR]
    MsgBox "EL CAMPO Employees"

e = 1

End If
Next cP

[B][COLOR=#008000]' Code for: if Row 4 contains both "E" 's and "R" 's in the cells[/COLOR][/B]
[B][COLOR=#008000]' ************************************************************************************************[/COLOR][/B]

If e + r = 2 Then

    For Each Cell In Range(Cells(4, 6), Cells(4, lCol)).SpecialCells(xlCellTypeVisible)
    If Cell = "R" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True
    CommandButton17.BackColor = 65535[COLOR=#008000] ' yellow for "E" & "R" 's[/COLOR]
[B][COLOR=#008000]' THIS CODE (ABOVE) TELLS IT TO HIDE THE COLUMNS VISIBLE ON THE SCREEN WITH AN "E" IN ROW 5 WHILST KEEPING THE VISIBLE COLUMNS ON THE SCREEN THAT CONTAIN AN "R" IN ROW 4. 
[/COLOR][/B]
Next

End If

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

There are a total of 3 possibilities that the code should identify;

1. It finds only "E"'s in the cells that are shown on the screen (thats why I have the "SpecialCells" in there... because before this commandbutton is executed, more than likely another event has already been ran narrowing down the columns before this command is started.)
2. It finds only "R" 's (in this case, it doesnt have to do anything as this is the result that it should display with this button anyway.)
3. It sees both "R" 's and "E" 's in the row (each cell will only contain an E or an R.. never both. And they will always have either an E or an R in the cell too. For this case it drills down to show only the columns that are shown on the screen that contain and R. (hiding the columns with an "E" in them in row 4.

some pictures to help understand what I am attempting to do:

Possibility 1 (option 1):

Possibility #2

And the third scenario (which will be the one usually encountered when running this code) is one where there will be a mixture of "E" 's and "R" 's in Row 5.

Currently the above code when executed does not STOP when one of the 3 possibilities is encountered. It continues to loop through 12 times before landing on one of them.

What do I have wrong in my code where it wont stop like it should once the correct possibility is identified? Thank you!

 

[shknbk2]

Try re-explaining what you want the code to do.

1. So what if it finds all Es? Hide the column if there is an 'a' in row 5 or simply just hide it no matter what?

2. So what if it finds all Rs? Do nothing?

3. So what if it finds Es and Rs? Should it hide the column with an E when it finds it but leave the R column alone?

4. What is the 'resetting' supposed to accomplish?

 

[kbishop94]

Try re-explaining what you want the code to do.

1. So what if it finds all Es? Hide the column if there is an 'a' in row 5 or simply just hide it no matter what?

2. So what if it finds all Rs? Do nothing?

3. So what if it finds Es and Rs? Should it hide the column with an E when it finds it but leave the R column alone?

4. What is the 'resetting' supposed to accomplish?

Thank you for your reply.

before addressing each of your questions, a little more back ground on what I am needing the code to do:

There will be 2 buttons that are basically the same (save for swapping out one of them for an "R" instead of an "E") The code I am trying to get working the way I need it to is for the "R" (Rosenberg) button. Once I have it I can just copy that code to the "E" button and swap out/switch a couple of strings/characters ("E" for "R" basically) and have the other one working too.

This short screen grab I made the other day should illustrate what the code is doing:

https://www.youtube.com/watch?v=X49HDPfgwzQ

As you can see in the top left corner of the sheet there are various colored buttons for departments. Upon choosing one it narrows down names linked to that department. (it narrows it down by hiding all the columns not linked to each name. )

Once that is done, then my new button/code will narrow it down further to by the remaining columns that do not contain an "R" (and the other button will do the same but for when they do not contain an "E".) (you can see how all that works in the :23 second video above.)

1. If after executing the button ("Rosenberg employees" button) there is only "E" 's visible, then it performs the code in my re-set button (basically all un-hiding the names... (the video is when I was trying to get it working by using only 1 button and 'toggle' between "R" and "E" 's, but ultimately I couldnt get this to work. )

2. All "R" 's , then do nothing.

3. "E" 's and "R" 's, then filter out the "E" columns.

4. answer in point # 1 .

Hope all that makes sense and you're able to understand what I am needing the code to do.

LIke I explained, i basically already have it working to do what I need it to do, but the only problem is it goes into a long loop. I need to have it STOP when each "If" or "Else" or "ElseIf" is encountered.

I think I know where the issue is, but again, I am not skilled enough to make that happen.

Here is where I believe the hold is:

For Each cP In Range(Cells(4, 6), Cells(4, lCol)).SpecialCells(xlCellTypeVisible)

If cP.Value = "R" Then

Its looping as it goes through EACH cell in that range looking for either "E", "R" or both.

What I think will work is if it looks in that range, and if it finds all the cells are all "E", then it assigns a value to that possibility; if it finds that its all "R" 's, then assigns a different value for that possibility, and if it finds a combination (any) of "E" 's and "R" 's then a value for that.

Then I can use my above code for doing each execution for each of those 3 new values/possibilities that will get assigned. (at least I think thats what I need to make it work(?) )

Thanks for reading all of this

 

[shknbk2]

I think I understand it. Try this code to find the Rosenberg employees:

Private Sub CommandButton28_Click()

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    ActiveWindow.ScrollColumn = 1

    Dim cP As Range
    Dim lCol As Long
    Dim nonRosenbergColumns() As Integer 'keep track of which columns are E columns
    Dim i As Long
    
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
    e = 0
    r = 0
    
    'First, go throught the cells to find how many 'E's and 'R's
    For Each cP In Range(Cells(4, 6), Cells(4, lCol)).SpecialCells(xlCellTypeVisible)
        If cP.Value = "R" Then
            r = r + 1
        ElseIf cP.Value = "E" Then
            'add column to list of E columns
            ReDim Preserve nonRosenbergColumns(e)
            nonRosenbergColumns(e) = cP.Column
            
            e = e + 1
        End If
    Next cP
    
    If e = 0 Then
        'Do nothing since all are Rosenberg employees
    ElseIf r > 0 Then  'mix of 'R's and 'E's
        
        'Hide the tracked columns from above
        For i = 0 To UBound(nonRosenbergColumns)
            Columns(nonRosenbergColumns(i)).Hidden = True
        Next
    Else 'reset since there are no Rosenberg employees (all 'E's)
        For Each Cell In Range(Cells(5, 6), Cells(5, lCol))
            If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
        Next
        For i = 3 To ActiveSheet.UsedRange.Columns.Count Step 2
            Columns(i).Hidden = Not Columns(i).Hidden
        Next i
    End If
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

 

[kbishop94]

I think I understand it. Try this code to find the Rosenberg employees:

That's got it. Works perfectly (meaning quickly!) for all 3 possibilities. Many, many, (many!) thanks to you shknbk2. That was the last little piece on my Training Matrix workbook I needed to fix/finalize. Thank you!

PS- is your user name a cue to a Talladega Nights reference? (if so, you are awesome sir. )

 

[shknbk2]

No problem. Glad to help.

No on the Talladega Nights reference. I haven't seen it, yet. I probably should. My brother was once upon a time shknbk, so I added the 2.

 

[kbishop94]

yea you need to. hilarious movie dude. thx again