View Full Version : Solved: Filtering with DTpicker
Rlb53
03-18-2012, 11:43 AM
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
03-18-2012, 03:09 PM
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
03-18-2012, 06:05 PM
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
03-18-2012, 11:53 PM
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
03-19-2012, 07:08 AM
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
03-19-2012, 07:05 PM
Thank You Kenneth !
Your guidance returned my Sanity.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.