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

 

[dmt32]

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

Dates in Autofilter can be a bit of a problem.
See if this approach helps:

Dave

Sub FilterDates()
Dim fromdate As Variant
Dim todate As Variant
Dim ldateto As Long
Dim ldatefrom As Long

fromdate = Application.InputBox("Please enter start date (in format yyyymmdd)", Type:=2)
If fromdate = False Then Exit Sub

todate = Application.InputBox("Please enter end date (in format yyyymmdd)", Type:=2)
If todate = False Then Exit Sub

ldatefrom = DateSerial(Year(fromdate), 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]

Thanks for responding so quickly, When I run the Macro I'm getting a Type Mismatch at

ldatefrom = DateSerial(Year(fromdate), Month(fromdate), Day(fromdate))
ldateto = DateSerial(Year(todate), Month(todate), Day(todate) + 1)

Running Excel version Office 2010 on Windows 7 64 Bit version. I can't test to see if this is related to 32Bit or 64Bit versions missing a system file.

 

[dmt32]

Thanks for responding so quickly, When I run the Macro I'm getting a Type Mismatch at

ldatefrom = DateSerial(Year(fromdate), Month(fromdate), Day(fromdate))
ldateto = DateSerial(Year(todate), Month(todate), Day(todate) + 1)

Running Excel version Office 2010 on Windows 7 64 Bit version. I can't test to see if this is related to 32Bit or 64Bit versions missing a system file.

Sorry, can't help you with that one - I am still using XP xl2003 & it works ok in that. Perhaps another can offer some guidance.

 

[dmt32]

Thanks for responding so quickly, When I run the Macro I'm getting a Type Mismatch at

ldatefrom = DateSerial(Year(fromdate), Month(fromdate), Day(fromdate))
ldateto = DateSerial(Year(todate), Month(todate), Day(todate) + 1)

Running Excel version Office 2010 on Windows 7 64 Bit version. I can't test to see if this is related to 32Bit or 64Bit versions missing a system file.

sorry, was in a bit of a hurry when I replied - I should have asked if you were entering a valid date in the inputbox?

Update code to test for valid date as follows & see if helps:

Datefrom:
    fromdate = Application.InputBox("Please enter start date (in format yyyymmdd)", 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 yyyymmdd)", Type:=2)
    If todate = False Then Exit Sub
    
    If Not IsDate(todate) Then MsgBox "Not A Valid Date": GoTo DateTo

 

[excelrcb3]

Yes, I entered the following Start Date 20120902 End Date 20120930. I change the code and it loops back to your code.

Datefrom:
fromdate = Application.InputBox("Please enter start date (in format yyyymmdd)", Type:=2)
If fromdate = False Then Exit Sub

If Not IsDate(fromdate) Then MsgBox "Not A Valid Date": GoTo Datefrom

 

[dmt32]

Yes, I entered the following Start Date 20120902 End Date 20120930. I change the code and it loops back to your code.

Datefrom:
fromdate = Application.InputBox("Please enter start date (in format yyyymmdd)", Type:=2)
If fromdate = False Then Exit Sub

If Not IsDate(fromdate) Then MsgBox "Not A Valid Date": GoTo Datefrom

It will do as what you are entering is not recognised as a date.
If you want to input your dates in the fomat shown, you can add additional code to convert your entry to a valid date format like this:

Datefrom:
    fromdate = Application.InputBox("Please enter start date (in format yyyymmdd)", Type:=2)
    If fromdate = False Then Exit Sub
    
    fromdate = Left(fromdate, 4) & "/" & _
                Mid(fromdate, 5, 2) & "/" & Right(fromdate, 2)

    If Not IsDate(fromdate) Then MsgBox "Not A Valid Date": GoTo Datefrom

DateTo:
    todate = Application.InputBox("Please enter end date (in format yyyymmdd)", Type:=2)
    If todate = False Then Exit Sub
    
    todate = Left(todate, 4) & "/" & _
                Mid(todate, 5, 2) & "/" & Right(todate, 2)
    
    If Not IsDate(todate) Then MsgBox "Not A Valid Date": GoTo DateTo

But of course, this all will assume that the data you have in field 4 is also recognised as a date when you run the Autofilter.


[

 

[excelrcb3]

Sorry for the confusion in my initial posting. The Data file is all Formatted as Text. Field 4 is formatted as text which is in the format yyyymmdd but is not formatted as a date. Would you recommend to format the range as a date? Or is there an easier way to get the autofilter to the Formatted text to select the ranges.

Original post;
I'm having a problem with an Auto Filtered Column trying to select Week Ending Dates which are Formatted in text as yyyymmdd

 

[dmt32]

Sorry for the confusion in my initial posting. The Data file is all Formatted as Text. Field 4 is formatted as text which is in the format yyyymmdd but is not formatted as a date. Would you recommend to format the range as a date? Or is there an easier way to get the autofilter to the Formatted text to select the ranges.

Original post;
I'm having a problem with an Auto Filtered Column trying to select Week Ending Dates which are Formatted in text as yyyymmdd

I've had too much going on today & did not fully read your post.
I would format the range to something that is more readable as a date

You can run this bit of code to cycle thru the range which should convert your text output to a date format. Datasheet is must be the activesheet. Hopefully you will then get the result you want. You should still be able to enter dates in the inputboxes in original format (ie 20120902) or you ca remove conversion code and ask user to enter date in normal format yyyy/mm/dd

Dave

Sub Convert2Date()
    Dim rng As Range
    Dim sdate As Range
    With ActiveSheet
        Set rng = .Range("D5:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
    End With
    For Each sdate In rng
        With sdate
            .Value = Left(.Value, 4) & "/" & _
                     Mid(.Value, 5, 2) & "/" & Right(.Value, 2)
                     
            .NumberFormat = "yyyy/mm/dd"
        End With
    Next
End Sub

 

[excelrcb3]

Dave, this is great stuff I'm learning a lot here's what I have now and it is clearing the filter without a selection, Thanks for your help!

Sub FilterDates()
'
' Macro2 Macro

Dim fromdate As Variant
Dim todate As Variant
Dim ldateto As Date
Dim ldatefrom As Date
Dim rng As Range
Dim sdate As Range


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
.Value = Left(.Value, 4) & "/" & _
Mid(.Value, 5, 2) & "/" & Right(.Value, 2)

.NumberFormat = "yyyy/mm/dd"
End With
Next

Datefrom:
fromdate = Application.InputBox("Please enter start date (in format yyyy/mm/dd)", Type:=2)
If fromdate = False Then Exit Sub

' fromdate = Left(fromdate, 4) & "/" & _
' Mid(fromdate, 5, 2) & "/" & Right(fromdate, 2)
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

'todate = Left(todate, 4) & "/" & _
'Mid(todate, 5, 2) & "/" & Right(todate, 2)

If Not IsDate(todate) Then MsgBox "Not A Valid Date": GoTo DateTo


ldatefrom = DateSerial(Year(fromdate), 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