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]

The conversion is working.

 

[dmt32]

Glad we got there in the end. As a thought, if you are incorporating the date conversation in your procedure you may want to add an IsDate test to ensure that you only convert the range once.
Something like this should work:

Dave

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

 

[excelrcb3]

The Filter is still clearly and I'm not sure I have the convert the range once in the proper location. Could you send where it would go?

 

[dmt32]

The Filter is still clearly and I'm not sure I have the convert the range once in the proper location. Could you send where it would go?

sorry, do not fully understand what it is you are asking.

 

[excelrcb3]

Filter is still clearing when the macro completes. I incorporated your code to select the range only once. and not sure if it is placed right. Here's what I have now.

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

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

'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

 

[excelrcb3]

Also if you notice I change the Dim ldateto As Long
Dim ldatefrom As Long
to
Dim ldateto As Date
Dim ldatefrom As Date

And I changed it back to Long and still getting the same results

 

[dmt32]

Also if you notice I change the Dim ldateto As Long
Dim ldatefrom As Long
to
Dim ldateto As Date
Dim ldatefrom As Date

And I changed it back to Long and still getting the same results

This as published works ok for me

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 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
    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

You had already spotted your error in changing the variables from a long data type to a date. The date is passed to the long variable using the DateSerial function & your amendment just confused it.
Also, you added the for next loop to convert the range twice which may or may not be havig an affect on your results.

Dave

 

[excelrcb3]

Hello

I have just returned from a long break and I ran the Code provided in the last posting. When I run the code, the .NumberFormat correctly formats the Range. When it gets to Datefrom and the Dateto part of the Sub, it returns the If not IsDate "Not A Valid Date" so I change the (in format yyyy/mm/dd) to (in format yyyy/mm/dd). The problem is that the .AutoFilter with the Criteria is still not selecting the Criteria Selections for the periods requested for instance, if I select the Start date to be 2012/09/02 and the End Date 2012/09/30 the expected result should be those dates and anything in between. The Filter does not make any selection as the value requested. Thanks

 

[excelrcb3]

Ignore last Thread

Correction;

Hello

I have just returned from a long break and I ran the Code provided in the last posting. When I run the code, the .NumberFormat correctly formats the Range. When it gets to Datefrom and the Dateto part of the Sub, it returns the If not IsDate "Not A Valid Date" so I change the (in format yyyymmdd) to (in format yyyy/mm/dd). The problem is that the .AutoFilter with the Criteria is still not selecting the Criteria Selections for the periods requested for instance, if I select the Start date to be 2012/09/02 and the End Date 2012/09/30 the expected result should be those dates and anything in between. The Filter does not make any selection as the value requested. Thanks​

 

[dmt32]

Ignore last Thread

Correction;

Hello

I have just returned from a long break and I ran the Code provided in the last posting. When I run the code, the .NumberFormat correctly formats the Range. When it gets to Datefrom and the Dateto part of the Sub, it returns the If not IsDate "Not A Valid Date" so I change the (in format yyyymmdd) to (in format yyyy/mm/dd). The problem is that the .AutoFilter with the Criteria is still not selecting the Criteria Selections for the periods requested for instance, if I select the Start date to be 2012/09/02 and the End Date 2012/09/30 the expected result should be those dates and anything in between. The Filter does not make any selection as the value requested. Thanks​

the code I last published worked ok for me on data you provided. If not working for you can only suggest that if you have made changes to go back & compare what you have to published code & change back any amendments in particular, if you have changed the variables to a different data type.

Also, may be worth adding CDate when converting text to date like this:

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

Hope helpful.