|
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 |
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 |
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 |
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 |
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 |
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 |
criteria for a given date equal or -greater then-
Thank you Jef,
I'll try to adopt the idea into my situation. "Jef Gorbach" wrote in message ... 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 |
| All times are GMT +1. The time now is 07:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com