filtering data

[royboy531]

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]

 

[iggydarsa]

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

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]

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]

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):

        .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:

.Range("a4:f4").AutoFilter

I hope it helps

 

[royboy531]

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]

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]

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]

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]

thanks iggydarsa. I finally got it to do what I wanted. your a lifesaver. thanks again

royboy531