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?
VBA code to filter by exact date via inputbox
-
-
-
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.
-
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
-
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? -
fotoDj maybe like this
CodeSub 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
-
-
[USER="275839"]graha_karya[/USER] 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
-
Is Your data in col j is date or try this give me feedback
Code
Display MoreSub 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
-
[USER="275839"]graha_karya[/USER] , still getting the same error on both t1 and t2, I am testing it on data input 10/24/2018 in the inputtbox
[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Capture123.JPG","data-attachmentid":1210813}[/ATTACH]
-
-
[USER="275839"]graha_karya[/USER] I am attaching my file...probably I keep making some kind of simple mistake, BTW it is Excel 2016. Appreciate your help and patience!
[ATTACH]n1210822[/ATTACH]
-
-
try this file
-
[USER="275839"]graha_karya[/USER] After any date input filter shows only first row...
-
Final working code:
CodeSub 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
-
[USER="334111"]toucherts[/USER] 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.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!