Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Using a Date Range with an Input Box and Selecting Data

  1. #1
    Join Date
    20th December 2012
    Posts
    7

    Using a Date Range with an Input Box and Selecting Data

    Hi,

    I am trying to build a macro in excel that will enable me to do the following:

    1. Open an input box where a start date would be entered then put that date in a specified cell on a specified sheet
    2. Open an input box where an end date would be entered then put that date in a specified cell on a specified sheet
    3. Select rows of data from a large dataset that falls between and/or on these dates
    4. Copy that data to another sheet.

    The ways I can do this are quite clunky and require a lot of coding - i.e. using a series of simpler functions on the sheet and in VBA

    I was hoping that someone who has more knowledge than I would be able to help me make this a smooth process.

    Any help would be greatly appreciated.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,898

    Re: Using a Date Range with an Input Box and Selecting Data

    Which version of Excel are you using?

    AutoFilter would probably be the best option, but an example workbook would help you get an answer.
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  3. #3
    Join Date
    20th December 2012
    Posts
    7

    Re: Using a Date Range with an Input Box and Selecting Data

    2007. I am not sure I can actually attach a file here...

    Esentially in Column A there are dates ranging from 2006 to the current day. I need to get a specific series of days out of that data while leaving that data undisturbed.

    As far as filters - I know how to code Filters in VBA however only with a constant (i.e. Set #3 out of 65 every time); not when the set varies each time I want to run the macro like I am trying to do. If you know of a way where I can use data residing in a cell in the workbook and use that to filter with then copy out the data then I am certainly game for it.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Using a Date Range with an Input Box and Selecting Data

    Here is the basic code example, no error handling included. Adjust per your needs.

    Sheet names are code names - not sheet tab names.
    Sheet2 is used to hold the filtered data and is first cleared of all previous data.

    VB:
    Option Explicit 
     
    Sub Data_Date_Filter() 
         
        Dim sDate As Variant, eDate As Variant 
         
        sDate = Application.InputBox("Enter the starting date as mm/dd/yyyy", Type:=1 + 2) 
        eDate = Application.InputBox("Enter the Ending date as mm/dd/yyyy", Type:=1 + 2) 
         
        Application.ScreenUpdating = False 
         
        Sheet2.Cells.ClearContents 
         
        With Sheet1 
            .AutoFilterMode = False 
            .Range("A1").CurrentRegion.AutoFilter field:=2, Criteria1:=">=" & sDate, Operator:=xlAnd, Criteria2:="<=" & eDate 
            .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Sheet2.Range("A1") 
        End With 
         
        Application.CutCopyMode = False 
        Application.ScreenUpdating = True 
         
    End Sub 
    
    
    I am not sure I can actually attach a file here
    If you get an upload error due to file size limitations use ZIP compression or a file share and provide the link.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Find Next Date In Range From Input
    By dunc1234 in forum EXCEL HELP
    Replies: 3
    Last Post: April 7th, 2008, 09:45
  2. Selecting Range Based On Date
    By flo123 in forum EXCEL HELP
    Replies: 6
    Last Post: February 13th, 2007, 04:55
  3. selecting input range for combo box
    By gazzapoh in forum EXCEL HELP
    Replies: 1
    Last Post: February 3rd, 2005, 09:22
  4. Selecting date range or single date
    By jsolo in forum EXCEL HELP
    Replies: 7
    Last Post: October 26th, 2004, 04:26
  5. Replies: 2
    Last Post: September 28th, 2004, 19:44

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno