trouble with locating a specific cell location (using offset?) for using 'If' / 'And' statement..

[kbishop94]

My code looks within a range of cells (employee names which are grouped by columns) and if each target cell does NOT contain an "X", (row 21 in the image below) then it keeps that name visible and hides all other ones that do contain an "X".

note the cells in the dark blue highlighted row with a white x and those with none:


The purpose of this is to provide a count and show which employees missed a specific training event (those with no 'x' missed the selected training event that was previously chosen via one of the two drop-down comboboxes.)

so the current code I have for hiding any cells with 'x's in them and keeping any blank cells unhidden looks like this:

(this code is executed via the command button shown in the next image circled in red w/ a red arrow pointing to it.)

For Each Cell In Range(Cells(12, 14), Cells(rCol, lCol)).SpecialCells(xlCellTypeVisible)
    If Cell.value = "" Then
        Columns(Cell.Column).Hidden = False
    Else
        Columns(Cell.Column).Hidden = True
    End If
Next

So what I am needing is to change the code to add an 'And' statement that will compare dates from 2 separate cells:
the two cells that I need it to compare are shown below. So row 8 contains the hire date for the employees shown. (I highlighted the one in column R in yellow w red font.)
the cell that each hire date is to be compared to is the date shown in column E in row 21 (again, yellow w red font.)
"If" the hire date for the employee shown is newer than the date shown in column E, then that name (column) is to be hidden along with the cells that do not contain an 'X'. (this is because the specific training date that was chosen happned before that employee was even hired so he (obviously) wouldn't of attended it.)


The dark blue highlighted row (row 23) represents the "For Each" range and is visible where the other non-matching rows are hidden.

The current line of code that shows:

If Cell.value = "" Then

I want to revise so that its saying (in layman terms!):

If the cell value is "" AND, if the date-of-hire value shown is older than the date shown in column E, then keep those cells UNHIDDEN and hide all the others that do not meet those 2 criteria.

So after running the code via the command button you will be left with this (only showing employees that missed the specific training event that happened IF they were hired when the event took place.)

(hope all that makes sense!) Thanks for any help.

 

[DanteAmor]

Try this:

  For Each Cell In Range(Cells(12, 14), Cells(rcol, lcol)).SpecialCells(xlCellTypeVisible)
    If Cell.Value = "" And Cells(8, Cell.Column) < Cells(Cell.Row, "E") Then
      Columns(Cell.Column).Hidden = False
    Else
      Columns(Cell.Column).Hidden = True
    End If
  Next

 

[kbishop94]

Try this:

  For Each Cell In Range(Cells(12, 14), Cells(rcol, lcol)).SpecialCells(xlCellTypeVisible)
    If Cell.Value = "" And Cells(8, Cell.Column) < Cells(Cell.Row, "E") Then
      Columns(Cell.Column).Hidden = False
    Else
      Columns(Cell.Column).Hidden = True
    End If
  Next

wow. I looked at the code and thought to myself "theres no way that that is going to work. its too simple." especially since I fully expected additional problems to arise even if the code did what I wanted it to... (if it correctly found and compared the specific cells that I needed it to.) This is because the formatting for the date cells in Row 8 is 'custom' and I really expected issues when comparing those 'custom' formatted cells to the 'regular' date formatted cells... But holy crap, your code worked 100% right out of the box!!! How amazing!

After filtering down to only show the employees who weren't yet working here for when the specific date training event happened that I selected form the drop down list (as represented by the dark blue row... row 23) it correctly shows just the employees that were working here for that specific training event and did not attend it. Well done and outstanding, DanteAmor!!!