fixing error object required "filter data between two value

A

abdelfattah

New member
Joined
Jul 17, 2020
Messages
10
Reaction score
0
Points
0
Excel Version(s)
2010
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: 
If Not IsEmpty(Enter.Range("E2")) And Not IsEmpty(Entry.Range("f2")) Then
HTML: 
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
 

Attachments

  • c.xlsm
    18.9 KB · Views: 20
Last edited:
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.
 
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"
 
Have you corrected the typos? If so please post the code you are currently using.
 
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
 
thanks bernie for rearrange the code but the problem still continue
 
hi, fluff the same code in post # 5 and i copy and paste again in module problem still continues
 
> 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....
 
You must log in or register to reply here.
Back
Top