Page 3 of 3 FirstFirst 1 2 3
Results 21 to 25 of 25

Thread: Basically change and function to or function

  1. #21
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,852
    Articles
    0
    Excel Version
    O365


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

    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 by Bob Phillips; 2014-04-17 at 11:01 AM.

  2. #22
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,852
    Articles
    0
    Excel Version
    O365
    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.

  3. #23
    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?

  4. #24
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,852
    Articles
    0
    Excel Version
    O365
    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

  5. #25
    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!

Page 3 of 3 FirstFirst 1 2 3

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
  •