criteria for a given date equal or -greater then-

[J_J]

Hi,
The below given code accepts a date (in "dd.mm.yyyy" format) entered into
the cell G3 of worksheet
"ARAMA" and then displays the records from the worksheet named "LISTE" that
matches with this criteria.
I need to modify it so that when I enter a date into Sheet "ARAMA" G3, I
want to display all records that has a date equal to or greater (newer) then
that date. Can experts here comment on modifying my code?
TIA

'--------------------------------
Public Sub Aratarih()

Range("I2").Value = Time
Range("I1").Value = Date

Range("A6:I2500").Select
Selection.ClearContents
ARANAN = Worksheets("ARAMA").Cells(3, 7)
Worksheets("ARAMA").Cells(3, 7).Activate
If ARANAN = "" Then
Range("A6:I2500").Select
Selection.ClearContents
Worksheets("ARAMA").Cells(4, 9).Value = ""
Worksheets("ARAMA").Cells(3, 7).Activate
'Range("I2").Value = Time
'Range("I1").Value = Date
Exit Sub
End If
With Worksheets("LISTE").Range("D262500")
Set C = .Find(ARANAN, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address
SAY = 0
Worksheets("ARAMA").Cells(4, 9).Value = "ARANIYOR"
Range("I2").Value = Time
Range("I1").Value = Date

Do
Set C = .FindNext(C)

adres = C.Address
adres = Right(adres, Len(adres) - 1)
ky = InStr(1, adres, "$", 1)
AD1 = Right(adres, Len(adres) - ky)
Worksheets("ARAMA").Cells(6 + SAY, 1).Value = SAY + 1
Worksheets("ARAMA").Cells(6 + SAY, 2).Value =
Trim(Worksheets("LISTE").Cells(AD1, 1).Value)
Worksheets("ARAMA").Cells(6 + SAY, 5).Value =
Trim(Worksheets("LISTE").Cells(AD1, 2).Value)
Worksheets("ARAMA").Cells(6 + SAY, 6).Value =
Trim(Worksheets("LISTE").Cells(AD1, 3).Value)
Worksheets("ARAMA").Cells(6 + SAY, 7).Value =
Trim(Worksheets("LISTE").Cells(AD1, 4).Value)
Worksheets("ARAMA").Cells(6 + SAY, 8).Value =
Trim(Worksheets("LISTE").Cells(AD1, 5).Value)
Worksheets("ARAMA").Cells(6 + SAY, 9).Value =
Trim(Worksheets("LISTE").Cells(AD1, 6).Value)
SAY = SAY + 1
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
Worksheets("ARAMA").Cells(4, 9).Value = SAY
End With
Range("G3").Select
End Sub

 

[Tom Ogilvy]

Think you would have to just loop through your column of dates and test each
one.

Hard to tell what you are doing - another possibility might be to use an
advanced filter or an autofilter.

 

[J_J]

Hi Tom,
I do not want to use an advanced filter or an autofilter although it appears
to be the easiest way out. Becuse the user of this workbook will be a
newbee.
The records are in sheet called LISTE as shown in the code, and all I need
to do is to check the dates (excluding empty cells) column there regarding
the date I'll enter on sheet ARAMA, and display the records on sheet ARAMA
with the same or newer dates. Hope I did explain what I intend to do...
Regards
J_J

 

[Tom Ogilvy]

If the code is applying and removing the filter, extensibly outside the
purview of the user, I am not sure what bearing the user's skill set has on
the code.

 

[J_J]

Tom, thank you for your comments.
J_J

If the code is applying and removing the filter, extensibly outside the
purview of the user, I am not sure what bearing the user's skill set has
on
the code.

 

[Jef Gorbach]

follow-up question for similar problem....any preference/benefits between
Advancefilter and Autofilter to reduce a running balance sheet covering
several years to only those rows within the prior month when end user is NOT
involved??

The functions seem todo the same thing, so guessing there is benefit of one
over other but havent found any comparisons on/offline.
Thanks.

<snip>
'ensure all rows have a date
finalrow = Cells(Rows.Count, 7).End(xlUp).Row
With Range("B1:B" & finalrow)
..SpecialCells(xlCellTypeBlanks).Value = 0
..NumberFormat = "mm-dd-yy"
End With

'hide last month's data then delete all visible rows
BOM = "<" & DateSerial(Year(Now), Month(Now) - 1, 1)
EOM = ">" & DateSerial(Year(Now), Month(Now), 0)
With Range(Cells(1, 1), Cells(finalrow, 7))
..AutoFilter Field:=2, Criteria1:=BOM, Operator:=xlOr, Criteria2:=EOM
..Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
..SpecialCells(xlCellTypeVisible).EntireRow.Delete
end with
activesheet.AutoFilterMode = False 'reveal remaining data
</snip>

 

[J_J]

Thank you Jef,
I'll try to adopt the idea into my situation.