filter sheet

s_samira_21 · #1 01-10-2015, 03:56 AM

Hi,

I have some data in sheet 1 and I want to filter them using an userform.
column A: Name
column B: Num1
column C: Num2
in my user form I have a combobox to select Name, 2 textboxes to enter min and max for Num1, and 2 other textboxes to enter min and max for Num2.

after I Enter the data the following code will be run by clicking on Filter Button

Code:

Private Sub CommandButton1_Click()
     Dim EOR
     If TextBox2.Text <> Empty And TextBox1.Text > TextBox2.Text Then               
          MsgBox "Wrong Numbers"              
          Exit Sub        
     End If        

     If TextBox4.Text <> Empty And TextBox3.Text > TextBox4.Text Then             
          MsgBox "Wrong Numbers"              
          Exit Sub       
     End If         

     Sheet1.AutoFilterMode = False        
     Sheet1.Range("A2:I1").AutoFilter            

     EOR = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row           
     Sheet1.AutoFilterMode = False      
     Sheet1.Range("A2:C" & EOR).AutoFilter         

     With Sheet1.Range("A3:C" & EOR)      
          If ComboBox1.Text <> Empty Then .AutoFilter Field:=1, Criteria1:="=" & ComboBox1.Text              
          If TextBox1.Text <> Empty Then .AutoFilter Field:=2, Criteria1:=">=" & TextBox1.Value               
          If TextBox2.Text <> Empty Then .AutoFilter Field:=2, Criteria1:="<=" & TextBox2.Value               
          If TextBox3.Text <> Empty Then .AutoFilter Field:=3, Criteria1:=">=" & TextBox3.Value               
          If TextBox4.Text <> Empty Then .AutoFilter Field:=3, Criteria1:="<=" & TextBox4.Value        
     End With   
End Sub

But it doesn't work correctly

please help me

the combobox and textboxes can be empty.

if it is better to use Advanced filter, Please help me with that.

whatsup · #2 01-10-2015, 08:27 AM

Hi

In order to filter on 2 criterias you will have to submit Criteria1 and Criteria2. The part for filtering has to look like this:

Code:

.
.
.
With Sheet1.Range("A3:C" & EOR)
        
        If ComboBox1.Text <> Empty Then
            .AutoFilter Field:=1, Criteria1:="=" & ComboBox1.Text
        Else
            Exit Sub
        End If
        If TextBox1.Text <> "" Then
            If TextBox2.Text <> "" Then
                .AutoFilter Field:=2, Criteria1:=">=" & TextBox1.Value, Criteria2:="<=" & TextBox2.Value
            Else
                .AutoFilter Field:=2, Criteria1:=">=" & TextBox1.Value
            End If
        Else
            If TextBox2.Text <> "" Then
                .AutoFilter Field:=2, Criteria1:=">=" & TextBox2.Value
            End If
        End If
        
        'If TextBox3.Text <> Empty Then .AutoFilter Field:=3, Criteria1:=">=" & TextBox3.Value
        'If TextBox4.Text <> Empty Then .AutoFilter Field:=3, Criteria2:="<=" & TextBox4.Value
    End With
End Sub

The same structure you will need for TextBox3 and 4

Cheers

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Create a New Sheet from Existing Sheet with Specific Columns	malam	Excel Programming	1	10-17-2014 10:01 PM
Is it possible to take a row on sheet 1 and transfer the info to sheet 2 by rows on conditions.	rogcar75	Excel	3	08-28-2014 05:21 PM
Help Coloring a cell in Sheet one if data is missing from another sheet	Aeducan	Excel	1	06-22-2014 04:49 PM
From an XL sheet ,how to keep the group of columns which match with other XL sheet	Zubairkhan	Excel	2	03-04-2014 10:57 PM
Construct a summary sheet by summing up from one or more than one sheet.	PRADEEPB270	Excel	1	11-04-2011 03:46 AM