Search/Find values in a column only if in date range recorded in different column

[SCOTTWHITTAKER2333]

Ok not sure if this is possible. I have a sheet and a userform that is used to track audits that employees complete. There are limits to what they can audit. A RTE or RAW type audit can not be duplicated in an 8 week rolling period. A Receiving type audit can be done a maximum of one time per period. And a genral/pallet type can only be done once per period.
What I would like to do is have a blank label lets say label1 on the userform change its caption from blank to a warning message if any of the above rules are broken from info entered into the userform by clicking a "check audit" button on the userform. Here is the Set up. On the sheet: Column A is names, Column B is date(short date format) that the audit was completed, C is the audit number name, D is the audit type and E is the period.
On the userform: L1 is a listbox of names, C1 is a calendar control to insert choosen dates, T1 is a textBox for the audit number name, L2 is a listbox with audit types and L4 is a listbox with periods in it.

Any assisatance would be wonderful

 

[SCOTTWHITTAKER2333]

Anybody?

 

[SCOTTWHITTAKER2333]

Ok this is what I have so far. I have edited some code that I found else where on this sight, that should do it but does not seem to be working correctly.
Here is the code:

Private Sub CommandButton3_Click()
 Dim WF As WorksheetFunction
    Dim a As Range, b As Range, c As Range, d As Range, e As Range
    Dim Arr1 As Variant, Arr2 As Variant, Arr3 As Variant, Arr4 As Variant
    Dim i As Integer
    Set WF = Application.WorksheetFunction
    
    Set a = Range("A2:A1800")
    Set b = Range("B2:B1800")
    Set c = Range("C2:C1800")
    Set d = Range("D2:D1800")
    Set e = Range("E2:E1800")
   If Me.L2.Value = "RTE" Or Me.L2.Value = "Raw" Then
    Arr1 = WF.Transpose(a)
    For i = 1 To UBound(Arr1)
        If Arr1(i) = Me.L1.Value Then
            Arr1(i) = 1
        Else
            Arr1(i) = 0
        End If
    Next i
    Arr2 = WF.Transpose(b)
    For i = 1 To UBound(Arr2)
        If Arr2(i) >= (Application.WorksheetFunction.WeekNum(Me.C1.Value) - 8) And Arr2(i) <= (Application.WorksheetFunction.WeekNum(Me.C1.Value)) Then
            Arr2(i) = 1
        Else
            Arr2(i) = 0
        End If
    Next i
    Arr3 = WF.Transpose(c)
    For i = 1 To UBound(Arr3)
        If Arr3(i) = Me.T1.Value Then
            Arr3(i) = 1
        Else
            Arr3(i) = 0
        End If
    Next i
    Arr4 = WF.Transpose(d)
    For i = 1 To UBound(Arr4)
        If Arr4(i) = Me.L2.Value Then
            Arr4(i) = 1
        Else
            Arr4(i) = 0
        End If
    Next i
    
    Me.L12.Caption = "There have been" & WF.SumProduct(Arr1, Arr2, Arr3, Arr4) & "of this type of audit completed in the last 8 weeks"
    Exit Sub
    ElseIf Me.L2.Value = "Genral/Pallet" Or Me.L2.Value = "Receiving" Then
    Arr1 = WF.Transpose(a)
    For i = 1 To UBound(Arr1)
        If Arr1(i) = Me.L1.Value Then
            Arr1(i) = 1
        Else
            Arr1(i) = 0
        End If
    Next i
    Arr2 = WF.Transpose(e)
    For i = 1 To UBound(Arr2)
        If Arr2(i) = Me.L4.Value Then
            Arr2(i) = 1
        Else
            Arr2(i) = 0
        End If
    Next i
    Arr3 = WF.Transpose(c)
    For i = 1 To UBound(Arr3)
        If Arr3(i) = Me.T1.Value Then
            Arr3(i) = 1
        Else
            Arr3(i) = 0
        End If
    Next i
    Arr4 = WF.Transpose(d)
    For i = 1 To UBound(Arr4)
        If Arr4(i) = Me.L2.Value Then
            Arr4(i) = 1
        Else
            Arr4(i) = 0
        End If
    Next i
    Arr5 = WF.Transpose(e)
    Me.L12.Caption = "There have been " & WF.SumProduct(Arr1, Arr2, Arr3, Arr4) & " of this type of audit completed in the last 8 weeks"
    
    End If
End Sub

I'm not sure but I think some of the problem has to do with the way i'm trying to do the date range.

 

[SCOTTWHITTAKER2333]

I have corrected the date issue by using a UDF:

Public Function ISOWeekNum(AnyDate As Date, Optional WhichFormat As Variant) As Integer
' WhichFormat: missing or <> 2 then returns week number,
'                                = 2 then YYWW
'
Dim ThisYear As Integer
Dim PreviousYearStart As Date
Dim ThisYearStart As Date
Dim NextYearStart As Date
Dim YearNum As Integer
ThisYear = Year(AnyDate)
ThisYearStart = YearStart(ThisYear)
PreviousYearStart = YearStart(ThisYear - 1)
NextYearStart = YearStart(ThisYear + 1)
Select Case AnyDate
    Case Is >= NextYearStart
        ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1
        YearNum = Year(AnyDate) + 1
    Case Is < ThisYearStart
        ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1
        YearNum = Year(AnyDate) - 1
    Case Else
        ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1
        YearNum = Year(AnyDate)
End Select
If IsMissing(WhichFormat) Then Exit Function
If WhichFormat = 2 Then
    ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _
    Format(ISOWeekNum, "00"))
End If
End Function

and

Public Function YearStart(WhichYear As Integer) As Date
Dim WeekDay As Integer
Dim NewYear As Date
NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7 'Generate weekday index where Monday = 0
If WeekDay < 4 Then
    YearStart = NewYear - WeekDay
Else
    YearStart = NewYear - WeekDay + 7
End If
End Function

so my code looks like this now:

Private Sub CommandButton3_Click()
 Dim WF As WorksheetFunction
    Dim a As Range, b As Range, c As Range, d As Range, e As Range
    Dim Arr1 As Variant, Arr2 As Variant, Arr3 As Variant, Arr4 As Variant
    Dim i As Long
    Set WF = Application.WorksheetFunction
    
    Set a = Range("A2:A1800")
    Set b = Range("B2:B1800")
    Set c = Range("C2:C1800")
    Set d = Range("D2:D1800")
    Set e = Range("E2:E1800")
   If Me.L2.Value = "RTE" Or Me.L2.Value = "Raw" Then
    Arr1 = WF.Transpose(a)
        For i = 1 To UBound(Arr1)
        If Arr1(i) = Me.L1.Value Then
            Arr1(i) = 1
        Else
            Arr1(i) = 0
        End If
        
    Next i
    
   
    Arr2 = WF.Transpose(b)
    For i = 1 To UBound(Arr2)
        If Arr2(i) >= (ISOWeekNum(Me.C1.Value, 1) - 8) And Arr2(i) <= ISOWeekNum(Me.C1.Value, 1) Then
            Arr2(i) = 1
        Else
            Arr2(i) = 0
        End If
    Next i
    Arr3 = WF.Transpose(c)
    For i = 1 To UBound(Arr3)
        If Arr3(i) = Me.T1.Value Then
            Arr3(i) = 1
        Else
            Arr3(i) = 0
        End If
    Next i
 
    Arr4 = WF.Transpose(d)
    For i = 1 To UBound(Arr4)
        If Arr4(i) = Me.L2.Value Then
            Arr4(i) = 1
        Else
            Arr4(i) = 0
        End If
    Next i
 
    Me.L12.Caption = "There have been " & WF.SumProduct(Arr1, Arr2, Arr3, Arr4) & " of this type of audit completed in the last 8 weeks"
    
    End If
End Sub

However it is still not functioning correctly.

Anyone see what I am doing wrong?

Oh, here is the link to were i found the UDF's

www.cpearson.com/excel/datetimevba.htm

 

[SCOTTWHITTAKER2333]

Ok i hve basicly cheated and just put in VBA to put the userform values on a hidden range in the sheet and then put a sumproduct in the hidden range to do the checking and then the l12.caption will just refre to the hidden cell. I didn't really like doing it this way but it works.
Here is the code:

If Me.L2.Value = "RTE" Or Me.L2.Value = "Raw" Then
    Range("K4") = add.L1.Text
    Range("K5") = add.C1.Value
    Range("K6") = add.T1.Value
    Range("K7") = add.L2.Text
    Range("m1") = "=SUMPRODUCT(--(A3:A2000=K4),--(B3:B2000>=(K5-40))*(B3:B2000<=(K5)),--(C3:C2000=K6),--(D3:D2000=K7))"
    L12.Caption = "There have been " & Range("m1") & " of this audit completed in the last 8 weeks by this employee"
    
    Exit Sub
    End If