Results 1 to 9 of 9

Thread: fixing error object required "filter data between two value

  1. #1
    Seeker abdelfattah's Avatar
    Join Date
    Jul 2020
    Posts
    7
    Articles
    0
    Excel Version
    2010

    fixing error object required "filter data between two value



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

    hello
    i try filter data based on two values in e2,f2 based on column a and copy to sheet2 but it gives me error in this line
    HTML Code:
    If Not IsEmpty(Enter.Range("E2")) And Not IsEmpty(Entry.Range("f2")) Then
    HTML Code:
    Sub filter()
    Dim ws As Worksheet
    Dim Enter As Worksheet
    Set ws = ThisWorkbook.Sheets("sheet2")
    Set Enter = ThisWorkbook.Sheets("sheet1")
    If Not IsEmpty(Enter.Range("E2")) And Not IsEmpty(Entry.Range("f2")) Then 
       With ws       
     .Range("$a$2:$a$" & .Range("a" & Rows.Count).End(xlUp).Row).AutoFilter Field:=1, _            Criteria1:=">" & Enter.Range("E2").Value, _            
    Operator:=xlAnd, _          
      Criteria2:="<" & Entry.Range("f2").Value   
     End With 
    End If
    End Sub
    thanks
    Attached Files Attached Files
    Last edited by abdelfattah; 2020-09-08 at 04:58 PM.

  2. #2
    Conjurer Fluff's Avatar
    Join Date
    Sep 2018
    Posts
    112
    Articles
    0
    Excel Version
    O365
    You have a typo on that line.
    Check the variable you have used for the sheet.
    If you use Option Explicit at the very top of the module it will help to spot that sort of error.

  3. #3
    Seeker abdelfattah's Avatar
    Join Date
    Jul 2020
    Posts
    7
    Articles
    0
    Excel Version
    2010
    hi, fluff
    i have found mistake about declare about variable the name is not match as what i use into code but it gives me strange error i try translate to English because my office not English
    run time error 1004"The command could not be completed using the specified range . Select one cell within a specified range and try executing the command again"

  4. #4
    Conjurer Fluff's Avatar
    Join Date
    Sep 2018
    Posts
    112
    Articles
    0
    Excel Version
    O365
    Have you corrected the typos? If so please post the code you are currently using.

  5. #5
    Acolyte Bernie's Avatar
    Join Date
    Feb 2018
    Location
    Suburban NY
    Posts
    34
    Articles
    0
    Excel Version
    Excel 2016 (Win)
    This code will treat A2 as the header - if A1 is the header, then change $a$2 to $A$1

    Code:
    Sub FixedFilterSub()    Dim ws As Worksheet
        Dim Enter As Worksheet
        Set ws = ThisWorkbook.Sheets("sheet2")
        Set Enter = ThisWorkbook.Sheets("sheet1")
        If Not IsEmpty(Enter.Range("E2")) And Not IsEmpty(Enter.Range("F2")) Then
            With ws
                .Range("$a$2:$a$" & .Range("a" & .Rows.Count).End(xlUp).Row).AutoFilter Field:=1, _
                Criteria1:=">" & Enter.Range("E2").Value, _
                Operator:=xlAnd, _
                Criteria2:="<" & Enter.Range("F2").Value
            End With
        End If
    End Sub
    Bernie Deitrick
    MS Excel MVP 2001-2010

  6. #6
    Seeker abdelfattah's Avatar
    Join Date
    Jul 2020
    Posts
    7
    Articles
    0
    Excel Version
    2010
    thanks bernie for rearrange the code but the problem still continue

  7. #7
    Seeker abdelfattah's Avatar
    Join Date
    Jul 2020
    Posts
    7
    Articles
    0
    Excel Version
    2010
    hi, fluff the same code in post # 5 and i copy and paste again in module problem still continues

  8. #8
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    812
    Articles
    0
    Excel Version
    Excel 2010
    Filter the Enter sheet instead of the blank one.

  9. #9
    Acolyte Bernie's Avatar
    Join Date
    Feb 2018
    Location
    Suburban NY
    Posts
    34
    Articles
    0
    Excel Version
    Excel 2016 (Win)
    > and < in filters does not work with strings, only with numbers. If you want to use that, you will need some conversion to number, like

    =CODE(LEFT(A2,1))

    Or you could create a list of the string values of interest, and then match the value against those....
    Bernie Deitrick
    MS Excel MVP 2001-2010

Posting Permissions

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