VBA code for an 'IF' statement that checks for a value within a range of visible columns... (ignoring hidden columns/cells)

[kbishop94]

Here is a picture of my spreadsheet and what I am trying to figure out how to get it to do what I need it to do:

The selected row (row 5) shows some cells that contain either a value of either "R" or "E". I need a If statement that checks a range of: Range(Cells(5, 6), Cells(5, lCol)), but only looks at visible cells(!)... not any cells(columns) that happen to be hidden at the time the code is executed.
There can be 3 possible out comes;
(1) If any ANY of the visible cells inside the range listed above it finds an "E", then (runs a macro for that outcome.)
(2) If any ANY of the visible cells inside the range listed above it finds an "R", then (runs a macro for that outcome.)
(3) If any ANY of the visible cells inside the range listed above it finds an "E" AND a "R", then (runs a macro for that outcome.)

Why I need this; This is a training matrix that keeps track of employees and what SOPs or documents that they have been trained for. The R and the E represent 2 different facilities. Depending on what is selected and currently being shown on the screen, it may be showing employees from 1 facility or another one, or a combination of both. Currently, I have 2 tabs on the spreadsheet representing our 2 facilities, but I want to integrate both of them together on 1 sheet, but I will still need a way to separate them for certain functions the various command buttons are scripted for. (hope that makes sense.) This is the only way I can think of that I could do this.... (but... was hoping someone else on here who is much smarter me might be able to suggest a better way, if there is one. )

Thanks!

 

[tonyyy]

kbishop94,

One approach would be to use the .SpecialCells method...

Dim r As Range
For Each r In Range(Cells(5, 6), Cells(5, lastCol)).SpecialCells(xlCellTypeVisible)
    If r.Value = "R" Then
        Call macro1
    ElseIf r.Value = "E" Then
        Call macro2
    ElseIf r.Value = "R" & "E" Then
        Call macro3
    End If
Next r

Cheers,

tonyyy

 

[kbishop94]

kbishop94,

One approach would be to use the .SpecialCells method...

Dim r As Range
For Each r In Range(Cells(5, 6), Cells(5, lastCol)).SpecialCells(xlCellTypeVisible)
    If r.Value = "R" Then
        Call macro1
    ElseIf r.Value = "E" Then
        Call macro2
    ElseIf r.Value = "R" & "E" Then
        Call macro3
    End If
Next r

Cheers,

tonyyy

Thanks tonyyy... I think the ElseIf is exactly what I need to do. But, I get "Compile error: Statement invalid outside Type block" when I run the code. (?)

Thanks

 

[tonyyy]

Which line of code is causing the error?

 

[kbishop94]

Correction, its not giving me that error afterall (It was, but it was something I must of entered incorrectly... I since have it working now, but I cannot get it to repeat with the same error with whatever i had in the code that it did not like.)

BUT... I still cannpt get it to work correctly.

The macro I have it directed to is just a simple line of code that changes the color of one of the commandbuttons based on which macro its being directed to (that way I can see which macro is running based on whether the "E"'s or the "R"s are showing in row 5. BUt I cannot get it to be directed to the macro that should be executed whenever both "E"'s and "R"'s are in the line. So in other words, its ignoring Macro3 when "R" and "E" are present in row5:

ElseIf r.Value = "R" & "E" Then
        Call macro3

ANy ideas about that?

thanks again

 

[kbishop94]

Which line of code is causing the error?

Here is all the complete code i am playing with right now in order to get it working:

Dim cP As Range
Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
For Each cP In Range(Cells(5, 6), Cells(5, lCol)).SpecialCells(xlCellTypeVisible)


    If cP.Value = "E" Then
        Call Macro6 ' BRIGHT PINK
        
        ElseIf cP.Value = "R" Then
        Call Macro7 ' BRIGHT GREEN
        
        ElseIf cP.Value = "E" & "R" Then
        Call Macro8 'BRIGHT BLUE
        
    End If


Next


End Sub

and 1 of the 3 temporary macros it executes when its triggered:

Sub Macro6()


' Macro for EL CAMPO Blending (Bl)
Worksheets("Current").cmdRosenbergElCampo.BackColor = 13382655 'BRIGHT PINK


End Sub

 

[tonyyy]

There's no indication if the cell contains ER or RE. The following accommodates both...

    ElseIf r.Value = "R" & "E" Or r.Value = "E" & "R" Then
        Call macro3

Cheers,

tonyyy

 

[kbishop94]

There's no indication if the cell contains ER or RE. The following accommodates both...

    ElseIf r.Value = "R" & "E" Or r.Value = "E" & "R" Then
        Call macro3

Cheers,

tonyyy

Hmmm... I get "Compile Error: Syntax error" when i enter that code.

Thanks for all your help btw. I appreciate it.

 

[tonyyy]

So, I know you're not using that code... since you're not using the variable "r" or the macro "macro3". Can you provide your line of code that's causing the error?

 

[kbishop94]

So, I know you're not using that code... since you're not using the variable "r" or the macro "macro3". Can you provide your line of code that's causing the error?

Well its not giving me the error (or at least it wasnt in your code), but I still cannot get it to work when there are both "R" and "E" 's in row 5. Ive tried re-arranging the order of the "R" and "E" in your code (putting the "E" to come first and vice-versus) and about anything else I could think of. Still won't run macro8 when "R" and "E" are in row 5.

Here is all the code:

Private Sub CommandButton2_Click()


    ' button for BLENDING (Bl)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


Dim cP As Range


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    
For Each cP In Range(Cells(5, 6), Cells(5, lCol)).SpecialCells(xlCellTypeVisible)


        If cP.Value = "E" Then
        Call Macro6 ' BRIGHT PINK

        ElseIf cP.Value = "R" Then
        Call Macro7 ' BRIGHT GREEN

        ElseIf cP.Value = "E" And "R" Or cP.Value = "R" & "E" Then
        Call Macro8 'BRIGHT BLUE
        
    End If
    
Next cP


End Sub