Good Afternoon,
I am having an issue creating the code below.
I have only ever used "Else" with 2 sets of criteria and need to complete this with 3 outcomes instead of 3 and I am missing something. It looks like I have an "If" possibly missing, but am unsure of where to enter it in the code, or I could be barking up the wrong tree.
Thanks in advance.
Gavin
I am having an issue creating the code below.
I have only ever used "Else" with 2 sets of criteria and need to complete this with 3 outcomes instead of 3 and I am missing something. It looks like I have an "If" possibly missing, but am unsure of where to enter it in the code, or I could be barking up the wrong tree.
Thanks in advance.
Gavin
Code:
Sub Open_Search()
Dim AgentName, CriteriaScore, CriteriaName As Variant
Dim Startdate, RaisedDate, Stopdate, Stopdate1 As Variant
Dim Ptype, Status, RaisedD, DDAte, PDescription, RefNo As Variant
Dim FoundMe As Boolean
Dim x, y As Integer
Dim LS, J As Long
Startdate = Worksheets("Advocate Data").Range("J8").Value
Stopdate = Worksheets("Advocate Data").Range("J4").Value
Stopdate1 = Worksheets("Advocate Data").Range("J12").Value
CriteriaName = Worksheets("Advocate Data").Range("K5").Value
Application.ScreenUpdating = False
Sheets("Import Open").Select
FoundMe = False
If Range("E22") = "" Then
Application.ScreenUpdating = True
MsgBox "Please Select Work Load to check", vbOKOnly, "Missing Workload"
Exit Sub
Else
CriteriaScore = Range("E22")
End If
'*******
x = 0
y = 0
Do
x = x + 1
Sheets("Import Open").Select
Range("E1").Select
AgentName = ActiveCell.Offset(x, 0).Value 'E
Ptype = ActiveCell.Offset(x, -1).Value 'D
Status = ActiveCell.Offset(x, -2).Value 'C
RaisedD = ActiveCell.Offset(x, -4).Value 'A
DDAte = ActiveCell.Offset(x, -3).Value 'B
PDescription = ActiveCell.Offset(x, 1).Value 'F
RefNo = ActiveCell.Offset(x, 2).Value 'G
If CriteriaScore = "Today" Then
If AgentName = CriteriaName And RaisedD = Startdate Then
y = y + 1
Sheets("Open").Select
Range("C24").Select
ActiveCell.Offset(y, 0).Value = RaisedD
Range("C24").Select
ActiveCell.Offset(y, 1).Value = DDAte
Range("C24").Select
ActiveCell.Offset(y, 3).Value = RefNo
Range("C24").Select
ActiveCell.Offset(x, 4).Select = Ptype
Range("C24").Select
ActiveCell.Offset(x, 6).Select = PDescription
FoundMe = True
End If
ActiveCell.Offset(x, 0).Select
Else
If CriteriaScore = "Over Due" Then
If AgentName = CriteriaName And RaisedD < Startdate Then
Do
y = y + 1
Sheets("Open").Select
Range("C24").Select
ActiveCell.Offset(y, 0).Value = RaisedD
Range("C24").Select
ActiveCell.Offset(y, 1).Value = DDAte
Range("C24").Select
ActiveCell.Offset(y, 3).Value = RefNo
Range("C24").Select
ActiveCell.Offset(x, 4).Select = Ptype
Range("C24").Select
ActiveCell.Offset(x, 6).Select = PDescription
FoundMe = True
'End If
ActiveCell.Offset(x, 0).Select
'End If
'Else
If CriteriaScore = "10 day Period" Then
If AgentName = CriteriaName And RaisedD >= Startdate And RaisedDate <= Stopdate1 Then
y = y + 1
Sheets("Open").Select
Range("C24").Select
ActiveCell.Offset(y, 0).Value = RaisedD
Range("C24").Select
ActiveCell.Offset(y, 1).Value = DDAte
Range("C24").Select
ActiveCell.Offset(y, 3).Value = RefNo
Range("C24").Select
ActiveCell.Offset(x, 4).Select = Ptype
Range("C24").Select
ActiveCell.Offset(x, 6).Select = PDescription
FoundMe = True
End If
ActiveCell.Offset(x, 0).Select
End If
Loop Until AgentName = ""
If FoundMe = False Then
MsgBox "Unable to Find any Quality Completed For " & CriteriaName & " for Quality Criteria " & CriteriaScore & ". Within the dates requested", vbOKOnly, "Please Try Again"
FoundMe = True
End If
Sheets("Today").Select
End If
End If
End If
End Sub