Announcement

Collapse
No announcement yet.

VBA code to filter by exact date via inputbox

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA code to filter by exact date via inputbox



    I need to filter Sheet1 by exact date using text inputbox - vba code. Dates are in column J... What is the easiest way to do it?

  • #2
    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.
    Alan

    Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
    FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

    If someone has helped you, say "thank you" by clicking on the Like Button.

    Comment


    • #3
      Your post does not comply with our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

      Post a link to any other forums where you have asked the same question.

      Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

      Go to https://www.excelguru.ca/content.php?184 to understand why we ask you to do this.

      I have added the crosspost reference for you today. Please comply with this and all our rules in the future

      https://www.mrexcel.com/forum/excel-...-vba-code.html
      Alan

      Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
      FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

      If someone has helped you, say "thank you" by clicking on the Like Button.

      Comment


      • #4
        Sorry about that double posting, I see your point now.
        Yes, user Momentman (thank you!) came up with that code below, which works after I formated properly the dates in column.
        Now all I guess all I need to do is need is modify that line Criteria1:=ActiveCell.Value to value from inputbox, right?

        Code:
        Sub FilterColumnJ()
        Dim Lastrow As Long 
        Lastrow = ActiveSheet.Range("J" & Rows.Count).End(xlUp).Row 
        ActiveSheet.Range("A1:J" & Lastrow).AutoFilter Field:=10,Criteria1:=ActiveCell.Value 
        End Sub

        Comment


        • #5
          fotoDj maybe like this
          Code:
          Sub FilterColumnJ()
           Dim Lastrow As Long
           Dim ap as string
           Dim dt as date,t1 as date
           ap = application.inputBox("get date")
           dt = cdate(ap)
           t1 =dateserial(dt,dt,dt)
           Lastrow = ActiveSheet.Range("J" & Rows.Count).End(xlUp).Row  
          ActiveSheet.Range("A1:J" & Lastrow).AutoFilter Field:=10,Criteria1:=t1
           End Sub

          Comment


          • #6
            graha_karya looks like code should work, but I'm getting msg "Run-time error "6" overflow" , maybe it is date format error? My date format in excel is formatted mm/dd/yyyy

            Comment


            • #7
              Is Your data in col j is date or try this give me feedback
              Code:
               Sub FilterColumnJ()
               Dim Lastrow As Long
               Dim ap as string
               Dim dt as date,t1 as date
               dim t2 as long
               ap = application.inputBox("get date")
               dt = cdate(ap)
               t1 =format(dateserial(dt,dt,dt),"mm/dd/yyyy")
               t2 = dt 'if  still error try change Criteria1 with t2
               Lastrow = ActiveSheet.Range("J" & Rows.Count).End(xlUp).Row  
               ActiveSheet.Range("A1:J" & Lastrow).AutoFilter Field:=10,Criteria1:=t1
               End Sub

              Comment


              • #8
                graha_karya , still getting the same error on both t1 and t2, I am testing it on data input 10/24/2018 in the inputtbox

                Click image for larger version

Name:	Capture123.JPG
Views:	0
Size:	51.5 KB
ID:	1210813

                Comment


                • #9
                  fotodj this work for for me
                  Code:
                  Sub ggg()
                  Dim ap, dt As Date, dt1 As Date, lr&: lr = ActiveSheet.UsedRange.SpecialCells(11).Row
                  With ActiveSheet.[a1].CurrentRegion.Resize(lr, 10)
                     ap = Application.InputBox("get date")
                     dt = CDate(ap)
                    .AutoFilter 10, "=" & dt
                  End With
                  End Sub

                  Comment


                  • #10
                    graha_karya I am attaching my file...probably I keep making some kind of simple mistake, BTW it is Excel 2016. Appreciate your help and patience!

                    Date_test.xlsm

                    Comment


                    • #11
                      try this file
                      Attached Files

                      Comment


                      • #12
                        graha_karya After any date input filter shows only first row...

                        Comment


                        • #13
                          Final working code:

                          Code:
                               Sub FilterDate()  
                                 Dim ap As Date, dt As Date, dt1 As Date     Dim lr As Long     lr = ActiveSheet.UsedRange.SpecialCells(11).Row    
                                 With ActiveSheet.[a1].CurrentRegion.Resize(lr, 10)        
                                 ap = Application.InputBox("get date")        
                                 dt = CDate(ap)      
                                .AutoFilter 10, Criteria1:=">=" & dt, _                
                                 Operator:=xlAnd, Criteria2:="<" & dt + 1  
                            End With End Sub

                          Comment


                          • #14
                            The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere.

                            Comment


                            • #15


                              toucherts Aren't you six month late with that comment, I apologized for that mistake already, you would see it if you've bothered to read more carefully, BTW you just joined up today and you school people already?
                              The last thing you want to do is waste people's time reading your useless remark.

                              Comment

                              Working...
                              X