Basically change and function to or function

rob179994

New member
Joined
Apr 9, 2014
Messages
17
Reaction score
0
Points
0
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.



RoadFolderChainage StartChainage End
A66A66-00153.565.5
A66A66-002
A66A66-00353.565.5
A66A66-0047779.5
A66A66-00552.555.5
A66A66-006
A66A66-007
A66A66-0084854.5
A66A66-009
A66A66-0104549.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 a moderator:

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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 ;)
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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).
 

rob179994

New member
Joined
Apr 9, 2014
Messages
17
Reaction score
0
Points
0
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!
 

rob179994

New member
Joined
Apr 9, 2014
Messages
17
Reaction score
0
Points
0
Actually, i have just been searching a few more values and it only worked for a couple for some reason...
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Tell us which values it does not work for.
 

rob179994

New member
Joined
Apr 9, 2014
Messages
17
Reaction score
0
Points
0
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?

RoadFolderChainage StartChainage End
A66A66-00153.565.5
A66A66-00353.565.5
A66A66-0047779.5
A66A66-00552.555.5
A66A66-0084854.5
A66A66-0104549.5
A66A66-0114146.5
A66A66-01437.540.5
A66A66-0173136.5
A66A66-0182130.5
A66A66-0192130.5
A66A66-0232130.5
A66A66-0259.522.5
A66A66-0269.522.5
A66A66-0279.522.5
A66A66-030410
A66A66-031410
A66A66-03796103
A66A66-03996103
A66A66-04096103
A66A66-04196103
A66A66-04296103
A66A66-043B96103
A66A66-0447475.5
A66A66-0467986
A66A66-0477986
A66A66-0487986
A66A66-0497986
A66A66-0549397
A66A66-0559397
A66A66-0579397
A66A66-059101.5104.5
A66A66-060101.5104.5
A66A66-063B101.5104.5
A66A66-0646164.5
A66A66-0656164.5
A66A66-0666475
A66A66-0677677
A66A66-06876.577.5
A66A66-0746.510.5
A66A66-0751213
A66A66-0804752
A66A66-083912
A66A66-08944.548
A66A66-0905457
A66A66-09135.543
A66A66-09235.543
A66A66-09734.539
A66A66-0985859
A66A66-0992932
A66A66-10022.524.5
A66A66-10101
A66A66-10251.559
A66A66-1032740.5
A66A66-1043133.5
A66A66-10595.5102.5
A66A66-12031.532
A66A66-120A5859
A66A66-120B5859
A66A66-12189.590.5
A590A590-0012837.5
A590A590-0022837.5
A590A590-00416.518.5
A590A590-0051819.5
A590A590-009810.5
A590A590-010810.5
A590A590-01123.526
A590A590-01211.513
A590A590-01311.513
A590A590-01419.522
A590A590-01521.522.5
A590A590-01613.515.5
A590A590-01713.515.5
A590A590-01878.5
A590A590-0193947.5
A590A590-0203947.5
A590A590-0243239
A590A590-0263239
A590A590-02905
A590A590-03005
A590A590-03105
A590A590-03539.545
A590A590-0363.510
A590A590-0371417.5
A590A590-0385.537.5
A590A590-0395.542.5
A590A590-04235
A590A590-0492123
A595A595-00918.529
A595A595-0111215.5
A595A595-0121112.5
A595A595-0152025
A595A595-0162025
A595A595-025
A595A595-0262627.5
A595A595-02811.517.5
A595A595-0363.55.5
A595A595-0372.55
A595A595-03803
A595A595-0422025
A595A595-0432025
A595NOT LISTED - A595-312025
A595NOT LISTED - A595-324.522
A595NOT LISTED - A595-33C1129
A595NOT LISTED - A595-341517
A595A595-0422025
A595A595-0432025
A595NOT LISTED - A595-312025
A595NOT LISTED - A595-324.522
A595NOT LISTED - A595-33C1129
A595NOT LISTED - A595-341517
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
... 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 :)
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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
 

rob179994

New member
Joined
Apr 9, 2014
Messages
17
Reaction score
0
Points
0
Thanks Bob, you have been a real help!

The only other thing i have got to do, is intergrate the road selected in to this search criteria. The value of the road is "Sheets("FrontSheet").ComboBox1.Value", how would i get this into the formula?

Finally, to call the column "Meets Criteria" instead of "tmp", would i just replace all the places that have "tmp" with "Meets Criteria" in the code above?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
The only other thing i have got to do, is intergrate the road selected in to this search criteria. The value of the road is "Sheets("FrontSheet").ComboBox1.Value", how would i get this into the formula?

In what way do you want to incorporate it?

Finally, to call the column "Meets Criteria" instead of "tmp", would i just replace all the places that have "tmp" with "Meets Criteria" in the code above?
Yes.
 

rob179994

New member
Joined
Apr 9, 2014
Messages
17
Reaction score
0
Points
0
Well say i search for certain marker posts on a road, it will bring up all folders with the marker posts specified but the folders will be for a mixture of roads. One of the options that i have is a ComboBox that has options "M6, M55, A66, A595, A590, A585". Therefore i want what ever the value of ComboBox1 on the Sheet "FrontPage" (Sheets("FrontSheet").ComboBox1.Value) to be the third criteria in the true or false statement! Is that possible and how do i do it?

Thanks for your help!
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
What sort of combobox, where is the source for that list, where is it linked to?
 

rob179994

New member
Joined
Apr 9, 2014
Messages
17
Reaction score
0
Points
0
It is an activex combobox and the options in the list are linked to this code.


Private Sub Workbook_Open()


With Sheet1.ComboBox1
.AddItem ""
.AddItem "M6"
.AddItem "M55"
.AddItem "A66"
.AddItem "A595"
.AddItem "A590"
.AddItem "A585"
End With




End Sub
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Code:
Sub Filter()
Dim cmbValue As String

    With Worksheets("FolderSort")
    
        cmbValue = .OLEObjects("ComboBox1").Object.Value
        
        .AutoFilterMode = False


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

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
You could also put that code in the ComboBox change event, so that selecting another value automatically filters the list.
 

rob179994

New member
Joined
Apr 9, 2014
Messages
17
Reaction score
0
Points
0
vba error.png

Thanks! For some reason, it brings up this message and I am way out my league now and I don't know what it means!
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Is the combobox on the FolderSort sheet?
 

rob179994

New member
Joined
Apr 9, 2014
Messages
17
Reaction score
0
Points
0
Nope, its on the "FrontPage" sheet with the chainage values you search!
 
Top