+ Reply to Thread
Results 1 to 13 of 13

Macro to search/filter raw data within date range

  1. #1
    Registered User
    Join Date
    10-19-2016
    Location
    Dhaka
    MS-Off Ver
    2013
    Posts
    54

    Macro to search/filter raw data within date range

    Hello My Expert Friends,

    I have my invoice data in sheet1 as raw data in various categories. Now I want make a report in sheet2 for specific date range for specific column. Report should generate in sheet2, when I set date range and click UPDATE button.

    (Picture 01) Raw data, it might full of thousands row

    Source Data 2.PNG

    (Picture 02) Report Format or How I want to extract/search/filter data

    Report Format 2.PNG


    Please help me how could I solve this issue in excel VBA environment.

    Thanks in Advance
    Last edited by iqbal.88; 05-16-2018 at 07:09 AM.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: Macro to search/filter raw data within date range

    You may try to record a macro with filtering your data and copying to second sheet. And then in the recorded code replace "hardcoded" dates to references to cells in sheet2.

    If that's too tough, please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Last edited by Kaper; 05-16-2018 at 08:02 AM.
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    10-19-2016
    Location
    Dhaka
    MS-Off Ver
    2013
    Posts
    54

    Re: Macro to search/filter raw data within date range

    Quote Originally Posted by Kaper View Post
    You may try to record a macro with filtering your data and copying to second sheet. And then in the recorded code replace "hardcoded" dates to references to cells in sheet2.

    If that's too tough, please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Dear Brother, thanks for your quick reply. In sheet2, where I want to make a summerised report, not every column in raw data . A loop should run/search on Column B:B in sheet1, matching the date in range & extract the date corresponding data from other column. Finally copy/post data in sheet2, when click update button.

    I attach the sample file herein, for your understanding.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Macro to search/filter raw data within date range


    Hi !

    Can be easily done without any code just using an advanced filter !
    And if really a code is needed, a single codeline is needed …

  5. #5
    Registered User
    Join Date
    10-19-2016
    Location
    Dhaka
    MS-Off Ver
    2013
    Posts
    54

    Re: Macro to search/filter raw data within date range

    Hello, Marc L

    Long before we meet another thread. How are you today?

    How can I solve this with VBA code? Please see my sample attachment, in previous reply.

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool


    As using a filter or an advanced filter is at beginner level (and just activating the Macro Recorder …)
    first apply these mods in Sheet2 :
    - enter Date in J1 & K1 cells (no typo allowed !).
    - J2 cell formula : =">="&DATEVALUE(C2)
    - K2 cell formula : ="<="&DATEVALUE(E2)

    These mods are necessary 'cause C2 & E2 cells do not respect Excel date rules and are not in the same format as Sheet1 …

    Once done just update the button code :

    PHP Code: 
    Private Sub CommandButton1_Click()
        
    Dim R&
        [
    A4].CurrentRegion.Columns(1).Offset(1).Clear
        Sheet5
    .ListObjects(1).Range.AdvancedFilter xlFilterCopy, [J1:K2], [B4:H4]
        
    = [A4].CurrentRegion.Rows.Count 1
        
    If 0 Then Range("A5:A" R).Value Evaluate("ROW(1:" ")")
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
    Last edited by Marc L; 05-16-2018 at 10:48 AM. Reason: typo …

  7. #7
    Registered User
    Join Date
    10-19-2016
    Location
    Dhaka
    MS-Off Ver
    2013
    Posts
    54

    Re: Macro to search/filter raw data within date range

    Dear Marc L Bro, Thanks for your reply, But sorry no result from your solution when click update button.
    Please remember, my raw data not limited to 10 or 12 rows only in sheet1; it might be thousands of row! then from there I should search data for a specific date or date range based on selected data in sheet2.

    Sol No Result.PNG
    Last edited by iqbal.88; 05-16-2018 at 01:23 PM.

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Macro to search/filter raw data within date range


    As I wrote, just enter the string Date (as a source header column) without any typo in J1 & K1 cells …

  9. #9
    Registered User
    Join Date
    10-19-2016
    Location
    Dhaka
    MS-Off Ver
    2013
    Posts
    54

    Re: Macro to search/filter raw data within date range

    Hello Marc L Bro, ok it will be helpful for me if you make a little brief, how your solution works here? please see the screenshot no result for specific date 31-03-218. your solution is tough to understand by me! can you guide me, how could I make this report with plain vba code running a loop function in source Date column?
    thanks

    Sol No Result 3.PNG

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow


    It works on my side on a computer (Excel 2010 / Windows 8.1) and fails today on another one (Excel 2003 / Seven)
    but works if operating manually an advanced filter or a filter.

    I have not much time to find out a workaround, so if someone else may help …

    K2 cell needs a mod for same day : ="<"&DATEVAL(E2)+1
    Last edited by Marc L; 05-17-2018 at 06:45 AM.

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb Maybe a workaround …


    After updating cell K2, delete code from worksheet module then create a new module (standard)
    and paste the next code and run it.
    If it works on your side like on mine, delete the button in worksheet and create a new one
    this time not an ActiveX but from Forms toolbar (so a normal button) then affect to it the Demo1 procedure …

    PHP Code: 
    Sub Demo1()
            
    Dim R&
        
    With Sheet1
            
    .[A4].CurrentRegion.Columns(1).Offset(1).Clear
             Sheet5
    .ListObjects(1).Range.AdvancedFilter xlFilterCopy, .[J1:K2], .[B4:H4]
             
    = .[A4].CurrentRegion.Rows.Count 1
             
    If 0 Then .Range("A5:A" R).Value Evaluate("ROW(1:" ")")
        
    End With
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

  12. #12
    Registered User
    Join Date
    10-19-2016
    Location
    Dhaka
    MS-Off Ver
    2013
    Posts
    54

    Re: Macro to search/filter raw data within date range

    Thanks Bro, it works now. But the solution seems to be not work perfectly with unstructured data/table or big amount of data. So I wish to solve the issue in code... lets me do that try next.

    Regards

  13. #13
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Macro to search/filter raw data within date range


    You can also use the table inner filter, train manually and once you succeed, cancel it
    then activate the Macro Recorder and redo the same filter.
    Once recording stops, cancel the filter and try to execute the recorded procedure : what do you observe ?

    I never had any issue with filter or advanced filter even on huge data …

    Other easy way : do not work with "text dates" but with real dates with the same format as source data.
    Last edited by Marc L; 05-17-2018 at 10:51 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to search data between date range
    By devawad in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-22-2017, 07:46 AM
  2. Replies: 4
    Last Post: 11-08-2014, 04:57 AM
  3. [SOLVED] Macro to filter date range
    By Excel_Monkey in forum Excel General
    Replies: 15
    Last Post: 10-02-2014, 09:23 AM
  4. Macro to filter data to search terms selected from ComboBox (Form Control)
    By JasonRay in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-08-2013, 05:08 PM
  5. Replies: 4
    Last Post: 10-10-2012, 03:38 PM
  6. Great Combo Filter/Search form, cant get DATE filter to integrate. HELP
    By fau5tu5 in forum Access Programming / VBA / Macros
    Replies: 4
    Last Post: 05-18-2009, 05:05 PM
  7. Macro to filter every tab from a user entered date range
    By penny in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-13-2007, 02:35 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1