Basically change and function to or function

Okay, the code then becomes

Code: 
Private Sub ComboBox1_Change()
Dim cmbValue As String


    With Worksheets("FolderSort")
    
        cmbValue = Worksheets("FrontPage").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
 
Last edited:
AS I say you could add a Combobox Change event to fir this code, and/or worksheet change events on E17 and K17 to also fire it, then activate FolderSort after the code is run.
 
Thanks Bob, Im not going to do the suggestion above as i want it to that when you have put in all the options you click search and it brings up the results!

However, i did think this was working but i just searched 0-105.5 for the A66 as that should bring up all folders as they are all within this range but it didnt bring up any apart from the empty missing folders and folder 101 which has range 0-1, do you know why this might be?
 
Try again

Code: 
Public Sub Filter()
Dim cmbValue As String

    With Worksheets("FolderSort")
    
        cmbValue = Worksheets("FrontPage").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,I5<=FrontPage!$K$17)," & _
                                            "AND(FrontPage!$E$17<=J5,J5<=FrontPage!$K$17)," & _
                                            "AND(I5<=FrontPage!$E$17,FrontPage!$K$17<=J5)),G5=""" & cmbValue & """)"
        .Range("K4:K368").AutoFilter Field:=1, Criteria1:="=TRUE"
    End With
End Sub
 
Thanks Bob, thats done it, i will test it out a lot more on Tuesday but i think thats it! You have been very helpfull, thanks again!
 
You must log in or register to reply here.
Back
Top