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.
There are a total of 3 possibilities that the code should identify;
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!
Code:
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;
- 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.)
- 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.)
- 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!
Last edited: