Filtering date field

mahhdy

Board Regular
Joined
Sep 15, 2016
Messages
86
Can anyone help?

I have problem with date filetr. My general idea is to let the team to put their input, chose the input type, their action and the code do the rest. So far, displaying the items.
My problem is by Date field entries. It shows nothing. I checked tha site and found I should use long data type, but it shows more and more errors.

If any idea instead of using goto, I will appreciated. I want to use it for coding for different actions.

Warmly yours,
M

Code:
Private Sub Go_Click()
'

Dim I As String ' specifies the input
Dim f As Integer ' for choosing the field
Dim a As String  ' for choosing the action
Dim d As Long    ' for date input
Dim t As String  ' specifies the input type
Dim C As ListObject 'for clearing Filters

t = ActiveSheet.Range("e3").Value
I = ActiveSheet.Range("g3").Value
a = ActiveSheet.Range("f3").Value
If I = "0" Then
Sheets("FTV4_Materials").Range("tbl").AutoFilter
Else
    If a = "" Then
        GoTo V
    Else
        Select Case a
                    Case "View Materials"
                        GoTo V
                    Case "close"
                        GoTo C
                    Case "issue KIT"
                        GoTo I
                    Case "receive"
                        GoTo R
                     Case "issue Items"
                        GoTo II

        End Select
    End If
GoTo finalline


C:
Sheets("Dash").Range("a1") = "No Data Close"
GoTo finalline
R:
Sheets("Dash").Range("a1") = "No Data Receive"
GoTo finalline
I:
Sheets("Dash").Range("a1") = "No Data Issue KIT"
GoTo finalline
II:
Sheets("Dash").Range("a1") = "No Data Issue Items"
GoTo finalline
V:
        Select Case t
                    Case "W/B"
                        f = 3
                    Case "P/#"
                        f = 4
                    Case "O/#"
                        f = 3
                    Case "Receiving Date"
                     Sheets("FTV4_Materials").Range("tbl").AutoFilter
                            rcvddate = CLng(DateValue(I))
                            Sheets("FTV4_Materials").Range("tbl").AutoFilter Field:=12, Criteria1:=rcvddate, Operator:=xlFilterValues
                            Sheets("FTV4_Materials").Select
                            ActiveSheet.Range("tbl[[#Headers],[ID]]").Select
                        GoTo finalline
                    Case "R/N"
                        f = 13
                    Case "Descriptio"
                        f = 6
                End Select
            Sheets("FTV4_Materials").Range("tbl").AutoFilter
            Sheets("FTV4_Materials").Range("tbl").AutoFilter Field:=f, Criteria1:="=*" & I & "*", Operator:=xlAnd
            Sheets("FTV4_Materials").Select
            ActiveSheet.Range("tbl[[#Headers],[ID]]").Select
            
            
finalline:
    End If
    
End Sub
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Sektor

Well-known Member
Joined
May 6, 2011
Messages
2,874
Office Version
  1. 365
Platform
  1. Windows
Date criteria condition MUST be as String, but you have Long - that's why you're getting error. It must be in U.S. format: Criteria1:=">01/29/2016" (greater than January, 29).
 
Upvote 0

mahhdy

Board Regular
Joined
Sep 15, 2016
Messages
86
Thanks,
it worked for me, I omitted the
"Dim d As Long" line and changed the following line,
Any idea about goto section?
I know that I can put each completed code right after each case clause, but it is not butifull coding.
Yours,
M

Code:
                            Sheets("FTV4_Materials").Range("tbl").AutoFilter Field:=12, Criteria1:=Format(I, "[$-409]dd-mmm-yy;@"), Operator:=xlFilterValues
 
Upvote 0

Forum statistics

Threads
1,190,898
Messages
5,983,451
Members
439,843
Latest member
PlanetFitness

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top