1. I understood the 1 or 2 or 3 criteria part. That was the purpose of the Len … > 0 part
If Len(EmployeeName) > 0 And .Cells(i, 3).Value <> EmployeeName Then GoTo GetNext
If Len(Modality) > 0 And .Cells(i, 4).Value <> Modality Then GoTo GetNext
If Len(Department) > 0 And .Cells(i, 5).Value <> Department Then GoTo GetNext
2. Having some data to play with makes it easier, and I did find some bugs in my first macro
3. I really did not like the way the C2, C3, or C4 dropdowns showed the same entry multiple times so I add a Worksheet_Activate event to the Payroll Report sheet to make 3 unique lists and use those
Option Explicit
Private Sub Worksheet_Activate()
Dim r As Range
Dim i As Long
Dim sName As String, sModality As String, sDept As String
With Sheet7.Cells(1, 1).CurrentRegion
sName = .Cells(2, 3).Value & ","
sModality = .Cells(2, 5).Value & ","
sDept = .Cells(2, 1).Value & ","
For i = 3 To .Rows.Count
If InStr(sName, .Cells(i, 3).Value) = 0 Then sName = sName & .Cells(i, 3).Value & ","
If InStr(sModality, .Cells(i, 5).Value) = 0 Then sModality = sModality & .Cells(i, 5).Value & ","
If InStr(sDept, .Cells(i, 1).Value) = 0 Then sDept = sDept & .Cells(i, 1).Value & ","
Next i
If Right(sName, 1) = "," Then sName = Left(sName, Len(sName) - 1)
If Right(sModality, 1) = "," Then sModality = Left(sModality, Len(sModality) - 1)
If Right(sDept, 1) = "," Then sDept = Left(sDept, Len(sDept) - 1)
End With
With Sheet11.Range("C2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=sName
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = vbNullString
.ErrorTitle = vbNullString
.InputMessage = vbNullString
.ErrorMessage = vbNullString
.ShowInput = True
.ShowError = True
End With
With Sheet11.Range("C3").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=sModality
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = vbNullString
.ErrorTitle = vbNullString
.InputMessage = vbNullString
.ErrorMessage = vbNullString
.ShowInput = True
.ShowError = True
End With
With Sheet11.Range("C4").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=sDept
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = vbNullString
.ErrorTitle = vbNullString
.InputMessage = vbNullString
.ErrorMessage = vbNullString
.ShowInput = True
.ShowError = True
End With
End Sub
So here's some more to play with