Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Do without Loop

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Location
    Manchester
    Posts
    481
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Do without Loop

    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

    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

  2. #2
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,027
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Do without Loop

    You can set intermediary Ifs with ElseIf. So if your first condition is blank, your second condition is it contains the number 3, else do your else part:

    Code:
        If Range("E22") = "" Then
                Application.ScreenUpdating = True
                MsgBox "Please Select Work Load to check", vbOKOnly, "Missing Workload"
            Exit Sub
        ElseIf Range("E22") = 3 Then
            MsgBox "It is 3"
        Else
                criteriascore = Range("E22")
                
        End If
    But if you're checking the same value for all conditions, you could use Select Case instead:


    Code:
    Select Case Range("E22")
        Case ""
            Application.ScreenUpdating = True
            MsgBox "Please Select Work Load to check", vbOKOnly, "Missing Workload"
            Exit Sub
        Case 3
            MsgBox "It is 3"
        Case Else
           CriteriaScore = Range("E22")
    End Select
    Office 2010/365

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com