filtering data

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
I am trying to use an input box to sort data and paste in a different area on the spreadsheet. The data I want to sort is dates and shifts with some additional information. I am unfamiliar with writing codes but am trying to learn. Here is an example of what I am trying to sort -

Date shift product color
11/11/5 1 a white
11/11/5 1 b nat
11/11/5 2 a white
11/11/5 3 a white

I actually want 2 input boxes, one that asks for the date and one that asks for the shift. Any assistance I could get would be great.[/code][/list]
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
Hi Royboy,

I have a code that might be helpful for you...

on my sheet the filters are on the cells between A4 and F4
On the first column I have dates and the second column I have the products

and here is the code... maybe you can change it a little bit and make it work for your data

Code:
Private Sub btnFilter_Click()
    Dim startDate As String
    Dim stopDate As String
    Dim prodName As String
    
    'reads date from the userform
    startDate = tbStartDate.Value 'tbStartDate is the name of the textbox for Starting Date
    stopDate = tbEndDate.Value 'tbStopDate is the name of the textbox for Ending Date
    prodName = tbProdName 'tbProdName is the name of the textbox for product name
    
    'FILTERS
    With Worksheets("Sheet1")
        .AutoFilterMode = False
        .Range("a4:f4").AutoFilter
        .Range("a4:f4").AutoFilter Field:=2, Criteria1:=prodName
        .Range("a4:f4").AutoFilter Field:=1, Criteria1:=(">=" & startDate), _
            Operator:=xlAnd, Criteria2:=("<=" & stopDate)
        
    End With
End Sub
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
I appriciate your help. I am still having some issues. Are you just filtering the first 2 columns out of the 4. what are your column headers if you don't mind my asking. Like I said I am new at this and I am trying to finger my way through it. thanks again for your help.
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
The data from A4 to F4 is:
Date Product Price Cost Profit %

with this code I am filtering six columns however the cirterias are only on first 2 columnds (Date and Product):
Code:
        .Range("a4:f4").AutoFilter Field:=2, Criteria1:=prodName 
        .Range("a4:f4").AutoFilter Field:=1, Criteria1:=(">=" & startDate), _ 
            Operator:=xlAnd, Criteria2:=("<=" & stopDate)
Field1 refers to Date column
Field2 refers to Product column

It is filtering 6 columns, all the six cells from A4 to F4 has a small arrow by the cells meaning that they can be filtered manually on the spreadsheet as well because on the code I have this line:

Code:
.Range("a4:f4").AutoFilter

I hope it helps
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52

ADVERTISEMENT

when I try to run it I get a run-time error '424': object required
and when I hit the debug button it highlights:
startdate = tbstartdate.Value 'tbstartdate is the name of the textbox for the starting date
Do you know what might be causing this.
Another question, is your code running in conjunction with a macro. this is what my end result is but I have not recorded a macro yet.
thanks
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
On my userform I have 3 textboxes, and each textbox has a name. I named my textboxes as:

tbStartDate for Starting date
tbStopDate for Ending date and
tbProdName for the product name.

Make sure you name the textboxes properly
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52

ADVERTISEMENT

iggydarsa, I do appriciate all your efforts to help me. I am new at this like I said. What userform are you referring to? again thanks for your patience.
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
When you mention about inputboxes I assumed that those are on a userform...

To create a userform, goto VB editor (Alt+F11). From the menu Insert->Userform

You will see a blank userform and a toolbox by it.
with the help of the toolbox create 3 textboxes. And name them (right click on the textbox, choose properties... on the new frame you will see "(Name)"... do it for all 3 textboxes and name them tbStartDate, tbStopDate, tbProdName)

also add a commandbutton (name it btnFilter)

double click on the button, on the new window you will see a header of a code... write the code there under the header...

if you get stuck you can ask again...

ragards
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
thanks iggydarsa. I finally got it to do what I wanted. your a lifesaver. thanks again

royboy531
 

Watch MrExcel Video

Forum statistics

Threads
1,118,670
Messages
5,573,558
Members
412,537
Latest member
Mohamed_5966
Top