Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 25

Thread: Basically change and function to or function

  1. #1

    Question Basically change and function to or function



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, so i have the following data, this is just a little bit of the document. The chainage just relates to a marker post on the road e.g. chainage 53.5 = 53.5 km along the road.

    Anyway i am trying to make vba code that searches for any folder that holds information about a section of road. Say the section was chainage 50 to chainage 57, the relevant folders would be any that have either the "chainage start" or the "chainage end" within these boundaries (001,003,005,008).

    Below this data, i have written the code i have already got, the problem i am having is that at the moment both the "chainage start" value and the "chainage end" value have to be within this range but i want it to be either one or both of them values are in it as i am loosing valuable information.

    Any way to basically change the and to or? Thank you for the help in advance.



    Road Folder Chainage Start Chainage End
    A66 A66-001 53.5 65.5
    A66 A66-002
    A66 A66-003 53.5 65.5
    A66 A66-004 77 79.5
    A66 A66-005 52.5 55.5
    A66 A66-006
    A66 A66-007
    A66 A66-008 48 54.5
    A66 A66-009
    A66 A66-010 45 49.5






    Code:
    Sub Filter()
    
    
        Sheets("FolderSort").Select
        ActiveSheet.AutoFilterMode = False
    
    
        ActiveSheet.Range("$I$4:$J$368").AutoFilter Field:=1, Criteria1:=">=" & Sheets("FrontPage").Range("E17").Value, _
            Operator:=xlAnd, Criteria2:="<=" & Sheets("FrontPage").Range("K17").Value
        ActiveSheet.Range("$I$4:$J$368").AutoFilter Field:=2, Criteria1:=">=" & Sheets("FrontPage").Range("E17").Value, _
            Operator:=xlAnd, Criteria2:="<=" & Sheets("FrontPage").Range("K17").Value
    
    
    End Sub
    Last edited by Bob Phillips; 2014-04-15 at 07:21 PM. Reason: Added VBA tags

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Rob,

    I have noticed several threads you've started and got responses too. Yet you have not acknowledged those responses, to say thanks, or if they worked or not. Most people would be unlikely to keep helping you if you have not responded in appreciation or otherwise.... just a hint


  3. #3
    Code:
    Sub Filter()
     
        With WorkSheets("FolderSort")
        
            .AutoFilterMode = False
    
            If .Range("K4").Value <> "tmp" Then
            
                .Columns("K").Insert
                .Range("K4").Value = "tmp"
            End If
            .Range("K5:K368").Formula = "=OR(AND(I5>=FrontPage!$E$17,I5<=FrontPage!$K$17),AND(J5>=FrontPage!$E$17,J5<=FrontPage!$K$17))"
            .Range("K4:K368").AutoFilter Field:=1, Criteria1:="=TRUE"
        End With
    End Sub

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    ... or I might be wrong.... some people would be unlikely to keep helping you if you have not responded in appreciation or otherwise.... (me included).


  5. #5
    Thanks a lot Bob, that does the job! However i am not sure why... What does the following mean?


    If .Range("K4").Value <> "tmp" Then .Columns("K").Insert .Range("K4").Value = "tmp" End If
    Also, i'm not sure how the following line filters either the first column or the second...
    .Range("K5:K368").Formula = "=OR(AND(I5>=FrontPage!$E$17,I5<=FrontPage!$K$17),AND(J5>=FrontPage!$E$17,J5<=FrontPage!$K$17))"

    Your explanation will be greatly appreciated!

  6. #6
    Actually, i have just been searching a few more values and it only worked for a couple for some reason...

  7. #7
    Tell us which values it does not work for.

  8. #8

    Question

    Well here is the whole table i am using. I searched between 50 and 51. So it should bring up A66-008 and A66-080 but it didnt bring up anything as everything came back as false. Any ideas?

    Road Folder Chainage Start Chainage End
    A66 A66-001 53.5 65.5
    A66 A66-003 53.5 65.5
    A66 A66-004 77 79.5
    A66 A66-005 52.5 55.5
    A66 A66-008 48 54.5
    A66 A66-010 45 49.5
    A66 A66-011 41 46.5
    A66 A66-014 37.5 40.5
    A66 A66-017 31 36.5
    A66 A66-018 21 30.5
    A66 A66-019 21 30.5
    A66 A66-023 21 30.5
    A66 A66-025 9.5 22.5
    A66 A66-026 9.5 22.5
    A66 A66-027 9.5 22.5
    A66 A66-030 4 10
    A66 A66-031 4 10
    A66 A66-037 96 103
    A66 A66-039 96 103
    A66 A66-040 96 103
    A66 A66-041 96 103
    A66 A66-042 96 103
    A66 A66-043B 96 103
    A66 A66-044 74 75.5
    A66 A66-046 79 86
    A66 A66-047 79 86
    A66 A66-048 79 86
    A66 A66-049 79 86
    A66 A66-054 93 97
    A66 A66-055 93 97
    A66 A66-057 93 97
    A66 A66-059 101.5 104.5
    A66 A66-060 101.5 104.5
    A66 A66-063B 101.5 104.5
    A66 A66-064 61 64.5
    A66 A66-065 61 64.5
    A66 A66-066 64 75
    A66 A66-067 76 77
    A66 A66-068 76.5 77.5
    A66 A66-074 6.5 10.5
    A66 A66-075 12 13
    A66 A66-080 47 52
    A66 A66-083 9 12
    A66 A66-089 44.5 48
    A66 A66-090 54 57
    A66 A66-091 35.5 43
    A66 A66-092 35.5 43
    A66 A66-097 34.5 39
    A66 A66-098 58 59
    A66 A66-099 29 32
    A66 A66-100 22.5 24.5
    A66 A66-101 0 1
    A66 A66-102 51.5 59
    A66 A66-103 27 40.5
    A66 A66-104 31 33.5
    A66 A66-105 95.5 102.5
    A66 A66-120 31.5 32
    A66 A66-120A 58 59
    A66 A66-120B 58 59
    A66 A66-121 89.5 90.5
    A590 A590-001 28 37.5
    A590 A590-002 28 37.5
    A590 A590-004 16.5 18.5
    A590 A590-005 18 19.5
    A590 A590-009 8 10.5
    A590 A590-010 8 10.5
    A590 A590-011 23.5 26
    A590 A590-012 11.5 13
    A590 A590-013 11.5 13
    A590 A590-014 19.5 22
    A590 A590-015 21.5 22.5
    A590 A590-016 13.5 15.5
    A590 A590-017 13.5 15.5
    A590 A590-018 7 8.5
    A590 A590-019 39 47.5
    A590 A590-020 39 47.5
    A590 A590-024 32 39
    A590 A590-026 32 39
    A590 A590-029 0 5
    A590 A590-030 0 5
    A590 A590-031 0 5
    A590 A590-035 39.5 45
    A590 A590-036 3.5 10
    A590 A590-037 14 17.5
    A590 A590-038 5.5 37.5
    A590 A590-039 5.5 42.5
    A590 A590-042 3 5
    A590 A590-049 21 23
    A595 A595-009 18.5 29
    A595 A595-011 12 15.5
    A595 A595-012 11 12.5
    A595 A595-015 20 25
    A595 A595-016 20 25
    A595 A595-025
    A595 A595-026 26 27.5
    A595 A595-028 11.5 17.5
    A595 A595-036 3.5 5.5
    A595 A595-037 2.5 5
    A595 A595-038 0 3
    A595 A595-042 20 25
    A595 A595-043 20 25
    A595 NOT LISTED - A595-31 20 25
    A595 NOT LISTED - A595-32 4.5 22
    A595 NOT LISTED - A595-33C 11 29
    A595 NOT LISTED - A595-34 15 17
    A595 A595-042 20 25
    A595 A595-043 20 25
    A595 NOT LISTED - A595-31 20 25
    A595 NOT LISTED - A595-32 4.5 22
    A595 NOT LISTED - A595-33C 11 29
    A595 NOT LISTED - A595-34 15 17

  9. #9
    Quote Originally Posted by NBVC View Post
    ... or I might be wrong.... some people would be unlikely to keep helping you if you have not responded in appreciation or otherwise.... (me included).
    I personally don't care about that, if they are happy with what we do, they come back

  10. #10
    I think my logic was not quite right. Try this version

    Code:
    Sub Filter() 
        With Worksheets("FolderSort")
        
            .AutoFilterMode = False
    
    
            If .Range("K4").Value <> "tmp" Then
            
                .Columns("K").Insert
                .Range("K4").Value = "tmp"
            End If
            .Range("K5:K368").Formula = "=OR(AND(FrontPage!$E$17>=I5,FrontPage!$E$17<=J5),AND(FrontPage!$K$17>=I5,FrontPage!$K$17<=J5))"
            .Range("K4:K368").AutoFilter Field:=1, Criteria1:="=TRUE"
        End With
    End Sub

Page 1 of 3 1 2 3 LastLast

Tags for this Thread

Posting Permissions

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