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]

Try changing the "And" to an ampersand &...

ElseIf cP.Value = "E" [COLOR=#ff0000]And [/COLOR]"R" Or cP.Value = "R" & "E" Then

ElseIf cP.Value = "E" [COLOR=#ff0000]& [/COLOR]"R" Or cP.Value = "R" & "E" Then

but I still cannot get it to work when there are both "R" and "E" 's in row 5

Now reading your latest post, I wonder if you're looking for R and E in the same cell (as your initial post requested) or that there's an R and an E in Row 5.

 

[tonyyy]

So if you're really testing the condition if there's an R and an E in Row 5, then...

Private Sub CommandButton2_Click()
    ' button for BLENDING (Bl)

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim cP As Range
Dim lCol As Long, e As Long, r As Long
lCol = ActiveSheet.UsedRange.Columns.Count
e = 0
r = 0

For Each cP In Range(Cells(5, 6), Cells(5, lCol)).SpecialCells(xlCellTypeVisible)
    If cP.Value = "E" Then
        Call Macro6 ' BRIGHT PINK
        e = 1
    ElseIf cP.Value = "R" Then
        Call Macro7 ' BRIGHT GREEN
        r = 1
    End If
Next cP

If e + r = 2 Then Call Macro8 'BRIGHT BLUE
End Sub

 

[kbishop94]

So if you're really testing the condition if there's an R and an E in Row 5, then...

Private Sub CommandButton2_Click()
    ' button for BLENDING (Bl)

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim cP As Range
Dim lCol As Long, e As Long, r As Long
lCol = ActiveSheet.UsedRange.Columns.Count
e = 0
r = 0

For Each cP In Range(Cells(5, 6), Cells(5, lCol)).SpecialCells(xlCellTypeVisible)
    If cP.Value = "E" Then
        Call Macro6 ' BRIGHT PINK
        e = 1
    ElseIf cP.Value = "R" Then
        Call Macro7 ' BRIGHT GREEN
        r = 1
    End If
Next cP

If e + r = 2 Then Call Macro8 'BRIGHT BLUE
End Sub

Thank you, Tony... that works brilliantly (nice thinking with assigning values to them like that to them sum them up to use as the other case when both E and R are present in the row. brilliant)

I apologize for not being clear for what I was needing (E and R in the same row... not in the same cell.) Sorry I wasted your time with all my back and forth about getting it to work correctly. But thanks again(!) I do really appreciate it.

 

[tonyyy]

You're very welcome, kbishop94. Happy to help...