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
    479
    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
    18,928
    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/2016

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
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com