Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start and end criteria macro
Does anyone know how to create a macro that when specific criteria i
input to input boxes it searches and copys data between the two. i.e. if i had data filed by dates and i wanted to pull information fro the sheet to copy to a report. I would enter the start date and the the end date. The end date must be last end date if there a tw enteries for the same date. the info would then be selected for cop and paste. Regards Pau -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start and end criteria macro
You can use an AutoFilter to filter for data in a date range, then copy
the visible cells to a different sheet. The following macro was adapted from code posted by Tom Ogilvy: http://www.contextures.com/xlautofilter03.html#Copy '===================================== Sub CopyDateRange() Dim rng As Range Dim rng2 As Range Dim strStart As String Dim strEnd As String strStart = Application.InputBox("Start Date") strEnd = Application.InputBox("End Date") Selection.AutoFilter Field:=1, _ Criteria1:="=" & CDate(strStart), Operator:=xlAnd, _ Criteria2:="<=" & CDate(strEnd) With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng2 = .SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With If rng2 Is Nothing Then MsgBox "No dates in that range" Else Worksheets("Sheet2").Cells.Clear Set rng = ActiveSheet.AutoFilter.Range rng.Copy Destination:=Worksheets("Sheet2").Range("A1") End If ActiveSheet.ShowAllData End Sub '========================================== xsilverx wrote: Does anyone know how to create a macro that when specific criteria is input to input boxes it searches and copys data between the two. i.e. if i had data filed by dates and i wanted to pull information from the sheet to copy to a report. I would enter the start date and then the end date. The end date must be last end date if there a two enteries for the same date. the info would then be selected for copy and paste. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start and end criteria macro
Thank you for explainging the above.
I have adapted part of the suggested VBA for my project and only use the part: Dim strStart As String Dim strEnd As String strStart = Application.InputBox("Start Date, (dd/mm/yyyy)") strEnd = Application.InputBox("End Date, (dd/mm/yyyy)") Selection.AutoFilter Field:=1, _ Criteria1:="=" & CDate(strStart), Operator:=xlAnd, _ Criteria2:="<=" & CDate(strEnd) It all works ok but after the filter there is no displayed data. I have tried the record macro function and adapted in parts for input but this does not resolve the problem. This problem does not occur when the custom filter is used manually. I do require the macro as the spreadsheet is used by a number of people with different levels of IT knowlege. Regards Paul --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start and end criteria macro
Try converting the dates to serial numbers:
Selection.AutoFilter Field:=1, _ Criteria1:="=" & CLng(CDate(strStart)), Operator:=xlAnd, _ Criteria2:="<=" & CLng(CDate(strEnd)) xsilverx wrote: Thank you for explainging the above. I have adapted part of the suggested VBA for my project and only use the part: Dim strStart As String Dim strEnd As String strStart = Application.InputBox("Start Date, (dd/mm/yyyy)") strEnd = Application.InputBox("End Date, (dd/mm/yyyy)") Selection.AutoFilter Field:=1, _ Criteria1:="=" & CDate(strStart), Operator:=xlAnd, _ Criteria2:="<=" & CDate(strEnd) It all works ok but after the filter there is no displayed data. I have tried the record macro function and adapted in parts for input but this does not resolve the problem. This problem does not occur when the custom filter is used manually. I do require the macro as the spreadsheet is used by a number of people with different levels of IT knowlege. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start and end criteria macro
Thank you very much Debra! It is now working fine!!
:D :D : -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need formula and not sure where to start! Matching criteria. | Excel Worksheet Functions | |||
Sub Macro vrs Function Macro Auto Start | Excel Discussion (Misc queries) | |||
How to start a Macro | Excel Discussion (Misc queries) | |||
I need a criteria expression to not include records that start wit | Excel Worksheet Functions | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |