Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > criteria for a given date equal or -greater then-
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 140
Default criteria for a given date equal or -greater then-
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("D2:D62500")
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




Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default criteria for a given date equal or -greater then-
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.

--
Regards,
Tom Ogilvy

"J_J" wrote in message
...
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("D2:D62500")
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






Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 140
Default criteria for a given date equal or -greater then-
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"J_J" wrote in message
...
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("D2:D62500")
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








Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default criteria for a given date equal or -greater then-
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.

--
Regards,
Tom Ogilvy

"J_J" wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"J_J" wrote in message
...
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("D2:D62500")
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










Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 140
Default criteria for a given date equal or -greater then-
Tom, thank you for your comments.
J_J

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"J_J" wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"J_J" wrote in message
...
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("D2:D62500")
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












Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 59
Default criteria for a given date equal or -greater then-
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" wrote in message
...
Tom, thank you for your comments.
J_J

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"J_J" wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"J_J" wrote in message
...
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("D2:D62500")
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














Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Add criteria to sumproduct Greater than or equal to Diddy Excel Worksheet Functions 5 September 22nd 09 02:50 PM
sumif date is greater than or equal chosen date Gary Excel Discussion (Misc queries) 2 September 19th 08 09:51 AM
sort data rows "greater than or equal" criteria in another cell HV man Excel Worksheet Functions 0 March 26th 06 11:50 PM
Addition to Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 9th 04 02:06 AM
Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 7th 04 10:50 PM


All times are GMT +1. The time now is 10:13 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement