Application InputBox Help

[excelrcb3]

Hello,

I'm having a problem with an Auto Filtered Column trying to select Week Ending Dates which are Formatted in text as yyyymmdd. I want the sub procedure to have user select only a Start Date and an End Date for a monthly period.
I can get the inbox to perfom with both periods, but when the sub completes it is not selecting the inputted request. It clears the filter with no dates.

Here's what I have;

ActiveSheet.Range("$A$4:$Z$15920").AutoFilter Field:=4
Columns("D:D").Select
Selection.NumberFormat = "yyyymmdd;@"
ActiveSheet.Range("$A$4:$Z$15920").AutoFilter Field:=4, Criteria1:=">=" & DateValue(Application.InputBox("Please enter start date (in format yyyymmdd)", Type:=2)), _
Operator:=xlAnd, Criteria2:="<=" & DateValue(Application.InputBox("Please enter end date (in format yyyymmdd)", Type:=2)) + 1
ActiveSheet.Range("$A$4:$Z$15920").AutoFilter Field:=4

Any help would be apprecicated. Thanks

rcb3excel

 

[excelrcb3]

Thanks but I'm not sure where to add the code you just provided. Could you attach your test worksheet where this is working so I can follow? I'm using your code without any amendments and getting the same results. There is a lot of Data in my work sheet which is proprieatary other wise I would attach the data I'm working from.

 

[dmt32]

post code you are using & will take another look

Dave

 

[excelrcb3]

Thanks for all your help!

Sub FilterDates()
    Dim fromdate As Variant
    Dim todate As Variant
    Dim ldateto As Long
    Dim ldatefrom As Long
    
    ChDir "C:\StopLight"
     Workbooks.Open Filename:="C:\StopLight\EBS_SWIFT_Data2.xlsx"
    With ActiveSheet
        Set rng = .Range("D5:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
    End With
    For Each sdate In rng
        With sdate
            If Not IsDate(.Value) Then
                .Value = Left(.Value, 4) & "/" & _
                         Mid(.Value, 5, 2) & "/" & Right(.Value, 2)
                .NumberFormat = "yyyy/mm/dd"
            End If
        End With
    Next
Datefrom:
    fromdate = Application.InputBox("Please enter start date (in format yyyy/mm/dd)", Type:=2)
    If fromdate = False Then Exit Sub
    If Not IsDate(fromdate) Then MsgBox "Not A Valid Date": GoTo Datefrom
DateTo:
    todate = Application.InputBox("Please enter end date (in format yyyy/mm/dd)", Type:=2)
    If todate = False Then Exit Sub
    If Not IsDate(todate) Then MsgBox "Not A Valid Date": GoTo DateTo
    ldatefrom = DateSerial(Year(todate), Month(fromdate), Day(fromdate))
    ldateto = DateSerial(Year(todate), Month(todate), Day(todate) + 1)
    ActiveSheet.Range("$A$4:$Z$15920").AutoFilter Field:=4, _
                                                  Criteria1:=">=" & ldatefrom, _
                                                  Operator:=xlAnd, _
                                                  Criteria2:="<=" & ldateto
End Sub

Rick

 

[dmt32]

tested it & works ok for me. I added Cdate see if that helps. Other than that, I am at loss if code continues to fail for you.

Dave

Sub FilterDates()
    Dim fromdate As Variant
    Dim todate As Variant
    Dim ldateto As Long
    Dim ldatefrom As Long
    
    ChDir "C:\StopLight"
     Workbooks.Open Filename:="C:\StopLight\EBS_SWIFT_Data2.xlsx"
    With ActiveSheet
        Set rng = .Range("D5:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
    End With
    For Each sdate In rng
        With sdate
            If Not IsDate(.Value) Then
                .Value = CDate(Left(.Value, 4) & "/" & _
                         Mid(.Value, 5, 2) & "/" & Right(.Value, 2))
                .NumberFormat = "yyyy/mm/dd"
            End If
        End With
    Next
Datefrom:
    fromdate = Application.InputBox("Please enter start date (in format yyyy/mm/dd)", Type:=2)
    If fromdate = False Then Exit Sub
    If Not IsDate(fromdate) Then MsgBox "Not A Valid Date": GoTo Datefrom
DateTo:
    todate = Application.InputBox("Please enter end date (in format yyyy/mm/dd)", Type:=2)
    If todate = False Then Exit Sub
    If Not IsDate(todate) Then MsgBox "Not A Valid Date": GoTo DateTo
    ldatefrom = DateSerial(Year(todate), Month(fromdate), Day(fromdate))
    ldateto = DateSerial(Year(todate), Month(todate), Day(todate) + 1)
    ActiveSheet.Range("$A$4:$Z$15920").AutoFilter Field:=4, _
                                                  Criteria1:=">=" & ldatefrom, _
                                                  Operator:=xlAnd, _
                                                  Criteria2:="<=" & ldateto
End Sub

 

[excelrcb3]

I notice now that whe you changed the Cdate it is changing the Date format to mm/dd/yyyy. Thanks for all your help Dave it is continuing to fail and not providing the outcome on my end. Can I ask how you are testing?

 

[dmt32]

I notice now that whe you changed the Cdate it is changing the Date format to mm/dd/yyyy. Thanks for all your help Dave it is continuing to fail and not providing the outcome on my end. Can I ask how you are testing?

I pasted data below which you posted earlier into Col D Row 5 & then ran the macro which worked ok for me. You could trying replacing CDate with DateValue.


Dave
[TABLE="width: 48"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]20120902
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120903
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120904
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120905
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120906
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120907
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120908
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120909
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120910
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120911
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120912
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120913
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120914
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120915
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120916
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120917
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120918
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120919
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120920
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20120921
[/TD]
[/TR]
</TBODY>[/TABLE]