Solved: Filtering with DTpicker

[Rlb53]

Hello Again !

I'm attempting to filter information by a Date.
However, information may not have been recorded on this specific Date.
I would like for the filter to be amended to the last record placed prior to the Date Selected.

This is where I'm at so far:

With ActiveSheet.Range("a8", Range("a" & Rows.Count)).End(xlUp)
.AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, DTPicker1.Value)
.AutoFilter Field:=2, Criteria1:=plant_no.Value

Suggestions ??

[Rlb53]

I'm still working with some options.

Was wondering if I'm heading in the right direction?

I haven't worked out the "If" Variable yet. I get an "Object Variable or With Block Variable not set" error.

With ActiveSheet.Range("a8", Range("a" & Rows.Count)).End(xlUp)
Dim i As Integer
Dim Count As Long
DT = (DTPicker1 + Count)
Count = 0
For i = -1 To Count

line3:

If .Find(DT).ActiveSheet.Range("a8:a") = True Then
GoTo line4:
Else
GoTo line3:

End If
Next i

line4:
.AutoFilter field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, DT)

.AutoFilter field:=2, Criteria1:=plant_no.Value

end with

[Rlb53]

Continuing to work with my task... I've evolved it into another procedure which is getting me much closer to the desired result, but still not quite there. If i select a date of "3/18/2012" and there is an entry dated "3/13/2012". The code below passes the row of data dated "3/13/2012" and selects "2/28/2012".


' The Column of Dates
ActiveSheet.Range("a1000000").End(xlUp).Activate

line3:
If ActiveCell <= DTPicker1.Value Then
ActiveCell.Copy
Range("a1").Select
' temporary storage of the Filter Criteria2:
Selection.PasteSpecial
GoTo line4:
Else
' if previous Activecell <> or < DTPicker1.value the cell to review is moved up to the previous entry for evaluation.
ActiveCell.Offset(-1, 0).Activate
GoTo line3:
End If

line4:
' In some cases there is not an entry prior to the date selected so a Nullstring is returned and the Value would be "0.00".
If ActiveCell = NullString Then
Sheets("inv report form").Activate
Range("e1000000").End(xlUp).Offset(1, 0).Activate
ActiveCell = 0
Selection.NumberFormat = "###,##0.00"
Selection.HorizontalAlignment = xlRight
' Returns the selection process to the list of worksheets that need to be reviewed and starts the process over again.
Sheets("data sheet 2").Activate
ActiveCell.Offset(1, 0).Activate
GoTo line2:
End If

'filters the worksheet to return the values sought.
With ActiveSheet.Range("a8", Range("a" & Rows.Count)).End(xlUp)

.AutoFilter field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, Range("a1"))

.AutoFilter field:=2, Criteria1:=plant_no.Value

[Rlb53]

I'm still plugging away at my goal.

To Filter data in multiple worksheets based on DTpicker selection.

The condition I'm experiencing is that in all cases there is not a specific match to the DTpicker selection and I want to return the next earlier date in sequence. I "Thought" that a simple " <= " would take care of it.. .but that is apparently not the case.

When running the code earlier, if i selected a date of 3/19/2012 (which does not exist) and there was a record dated 3/17/2012, 3/14/2012, etc. The filter would pass the preferred record of 3/17/2012 and stop at 2/28/2012.

The latest configuration I've come up with converts the DTpicker values to General Format. (I thought this modification may refine (define) the filter)

Even with the change, the filter is still bypassing the values equal to 3/17, 3/13, etc. and stopping at the 2/28 value.

It looks like I'm at the end (and beyond) of my current abilities. If anyone has a suggestion, it would be Greatly appreciated.

line2:
Sheets(ActiveCell.Value).Activate
ActiveSheet.AutoFilterMode = False

ActiveSheet.Range("b3").Copy 'ProductID
Sheets("inv report form").Activate
Range("C2") = Format(DTPicker1, "mm/dd/yy")
Range("a1000000").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial (xlPasteValues)
Selection.HorizontalAlignment = xlCenter
Range("b1000000").End(xlUp).Offset(1, 0).Select
Selection.NumberFormat = "@"
Selection = Format(Me.Plant_No, "0#")
Selection.HorizontalAlignment = xlCenter
Sheets("data sheet 2").Activate
Sheets(ActiveCell.Value).Activate

ActiveSheet.Range("b4").Copy 'Description
Sheets("inv report form").Activate
Range("c1000000").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial (xlPasteValues)
Selection.HorizontalAlignment = xlCenter
Sheets("data sheet 2").Activate
Sheets(ActiveCell.Value).Activate

ActiveSheet.Range("c5").Copy 'Unit
Sheets("inv report form").Activate
Range("d1000000").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial (xlPasteValues)
Selection.HorizontalAlignment = xlCenter
Sheets("data sheet 2").Activate
Sheets(ActiveCell.Value).Activate
'
'This is where my Headache Starts...............................
'
ActiveSheet.Range("a:a").Select
Selection.NumberFormat = "general"


line3:
ActiveSheet.Range("b1") = DTPicker1
Range("b1").Select
Selection.NumberFormat = "general"
Range("b1").Value = Round(Range("b1"), 0)
ActiveSheet.Range("a1000000").End(xlUp).Activate

If Not IsNumeric(ActiveCell.Value) Then
Sheets("inv report form").Activate
Range("e1000000").End(xlUp).Offset(1, 0).Activate
ActiveCell = 0
Selection.NumberFormat = "###,##0.00"
Selection.HorizontalAlignment = xlRight
Sheets("data sheet 2").Activate
ActiveCell.Offset(1, 0).Activate
If ActiveCell.Value = NullString Then
GoTo line5:
Else
GoTo line2:
End If
End If

ActiveCell.Value = Round(ActiveCell, 0)

If ActiveCell <= ActiveSheet.Range("b1") Then
ActiveCell.Copy
Range("d1").Select
Selection.PasteSpecial
GoTo line4:
Else
ActiveCell.Offset(-1, 0).Activate
GoTo line3:
End If

line4:
Rows("8:8").Select
Selection.AutoFilter

With ActiveSheet.Range("a9", Range("a" & Rows.Count)).End(xlUp)

.AutoFilter field:=1, Criteria1:=Range("d1").Value

.AutoFilter field:=2, Criteria1:=Plant_No.Value
'
' This is where my Headache Ends......................Or so I think?
'
Range("o1").End(xlDown).Select
Range("o1000000").End(xlUp).Copy

Sheets("inv report form").Activate
Range("e1000000").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial (xlPasteValues)
If ActiveCell.Value = NullString Then
ActiveCell.Value = 0
End If
Selection.NumberFormat = "###,##0.00"
Selection.HorizontalAlignment = xlRight
Sheets("data sheet 2").Activate
Sheets(ActiveCell.Value).Activate
ActiveSheet.AutoFilterMode = False
Range("a:a").Select
Selection.NumberFormat = "m/d/yyyy"

End With
Sheets("data sheet 2").Activate
ActiveCell.Offset(1, 0).Activate

If ActiveCell.Value = NullString Then

line5:
Sheets("inv report form").Select
Cells.Select
Selection.Columns.AutoFit

Range("a1").Select

Unload Me

Range("e1000000").End(xlUp).Offset(1, 0).Activate
Set pr = Range("a1", ActiveCell)
ActiveSheet.PageSetup.PrintArea = pr
pr.PrintPreview
Range("a5", ActiveCell).Delete


Exit Sub
Else
GoTo line2:
End If


End Sub

[Kenneth Hobs]

A post that has 0 replies is more likely to be visited than one with 3.

It is hard to help when you post code that is not relevant or code that we can not test. It is easier to help you post a simple example that illustrates the problem. We don't need to see all that you have done. Isolate the problem is the key to the solution.

This works for me:
Private Sub CommandButton1_Click()
Range("H2").AutoFilter
Range("$H$1:$I$" & Range("H" & Rows.Count).End(xlUp).Row).AutoFilter _
Field:=1, Criteria1:=">" & DTPicker1.Value, Operator:=xlAnd
End Sub

[Rlb53]

Thank You Kenneth !

Your guidance returned my Sanity.