+ Reply to Thread
Results 1 to 2 of 2

Making lengthy AND statements succinct

Hybrid View

  1. #1
    Registered User
    Join Date
    02-18-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Making lengthy AND statements succinct

    Hi all, working the weekend... again.

    If anyone has any suggestions for shortening a lengthy AND statement down to something more pretty I will love you all forever as always

            lastrow = Cells(Rows.Count, 1).End(xlUp).Row
            For x = lastrow To 1 Step -1
                If Cells(x, 3) <> Route(1) And _
                   Cells(x, 3) <> Route(2) And _
                   Cells(x, 3) <> Route(3) And _
                   Cells(x, 3) <> Route(4) And _
                   Cells(x, 3) <> Route(5) And _
                   Cells(x, 3) <> Route(6) And _
                   Cells(x, 3) <> Route(7) And _
                   Cells(x, 3) <> Route(8) And _
                   Cells(x, 3) <> Route(9) And _
                   Cells(x, 3) <> Route(10) And _
                   Cells(x, 3) <> Route(11) And _
                   Cells(x, 3) <> Route(12) And _
                   Cells(x, 3) <> Route(13) And _
                   Cells(x, 3) <> Route(14) And _
                   Cells(x, 3) <> Route(15) And _
                   Cells(x, 3) <> Route(16) And _
                   Cells(x, 3) <> Route(17) And _
                   Cells(x, 3) <> Route(18) And _
                   Cells(x, 3) <> Route(19) And _
                   Cells(x, 3) <> Route(20) Then
                    Rows(x).Delete
                End If
            Next x
    This code isolates route numbers and only lets the ones defined in a range of cells that is then copied into the Route array variable. It works its just a little ugly.

  2. #2
    Registered User
    Join Date
    10-19-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Making lengthy AND statements succinct

    You can use match worksheet function
    Dim found as long
     On Error Resume Next
        found = WorksheetFunction.Match( Cells(x, 3), Route, 0)
        If Err = 0 Then
                 MsgBox "Found"
        Else
            Err.Clear
            MsgBox "Not Found"
        End If
        On Error GoTo 0

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1