+ Reply to Thread
Results 1 to 13 of 13

determine dynamic range and redetermine it again

  1. #1
    Forum Contributor
    Join Date
    11-12-2010
    Location
    HK
    MS-Off Ver
    Excel 2010
    Posts
    189

    determine dynamic range and redetermine it again

    Hi all,

    I just got a problem with determining dynamic ranges.
    As shown below, I would like to declare a range whose dates are between 01/9/2013 and 30/9/2013 so that I could do some lookup in this range. Then I would like to re-declare a range whose dates are between 01/8/2013 and 31/8/2013 and so on.
    How could I achieve the goal?
    Many Thanks!
    A B C D
    1 NE 19/9/2013 Transportation -4.4
    2 NE 18/9/2013 Transportation -4.4
    3 NE 17/9/2013 Transportation -4.4
    4 JC 13/9/2013 Other -10
    5 NE 10/9/2013 Transportation -40.1
    6 BC 8/9/2013 [Cash] -600
    7 TF 1/9/2013 ABV -500
    8 NE 31/8/2013 [Cash] 3398.5
    9 NE 13/8/2013 Transportation -40.1
    10 NE 5/8/2013 Transportation -4.4
    11 JS 4/8/2013 Joint -60
    Last edited by lubbamkt; 09-24-2013 at 07:36 PM.

  2. #2
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: determine dynamic range and redetermine it again

    What is the end goal of these determinations? It usually helps if you can give an example of before and after.

  3. #3
    Forum Contributor
    Join Date
    11-12-2010
    Location
    HK
    MS-Off Ver
    Excel 2010
    Posts
    189

    Re: determine dynamic range and redetermine it again

    My end goal is to sum the amount (Col D) under the same category (Col C) and in the same month (Col B). I originally intend to use FOR NEXT method to do the task, but acutally I got nearly 10,000 rows and 48 months, so I think this may not be a wise method to do so.

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: determine dynamic range and redetermine it again

    Hi lubbamkt,

    Here's a start:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  5. #5
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: determine dynamic range and redetermine it again

    my thoughts:

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: determine dynamic range and redetermine it again

    You could also always create a pivot table and then have two macros. One that refreshes the pivot range data before the query, and one that filter on the Pivot field range of date.

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: determine dynamic range and redetermine it again

    Hi,

    My answer above was only to the very first post #1 - It looks as though the criteria have changed.

    Can you post a sample spreadsheet and state your expectations? (Go to Advanced and click on the paper clip)

  8. #8
    Forum Contributor
    Join Date
    11-12-2010
    Location
    HK
    MS-Off Ver
    Excel 2010
    Posts
    189

    Re: determine dynamic range and redetermine it again

    Hi all,

    I attached a sample excel file.
    what I want to do is to sum corresponding amount from Sheet1 to Sheet2 based on
    1) date
    2) category
    For example, in Sheet2 B9 cell, I want to add those amount from Sheet1 under Transportation if they are dated between 1/9/2013 and 30/9/2013. In Sheet2 C13, add those amount from Sheet1 under Joint if they are dated between 1/8/2013 and 31/8/2013.

    In the beginning, I used FOR NEXT method to do the task, but later on I found that it takes very long time to run beacuse I have nearly 10,000 rows in Sheet1 and I need to look for 48 months data under nearly 50 categories. So each time to search a particular category (such as above Transportation) for a specified month (such as Sep 2013), vba have to run nearly 10,000cells in Sheet1 Date column to determine if they are dated between 1/9/2013 and 30/9/2013.

    Please advice. Thank you so much
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    11-12-2010
    Location
    HK
    MS-Off Ver
    Excel 2010
    Posts
    189
    Could anyone help me ?
    English is not my first language

  10. #10
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: determine dynamic range and redetermine it again

    Away Fromm computer on Saturday. However, if you create a pivot table, if you highlight the date cell and go to either the options or pivot table ribbon there is a group option that allows you to group by month. Record a macro while you do it and then you will have a good starting place for analysis.

  11. #11
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: determine dynamic range and redetermine it again

    Please Login or Register  to view this content.
    will help you if you write code to dynamically change the range, if inserted in the pivot table correctly.

  12. #12
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: determine dynamic range and redetermine it again

    "MyTable" Would take the place of Sheet1!A1:c24 in other words.

  13. #13
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: determine dynamic range and redetermine it again

    Hi,

    Try this:

    Please Login or Register  to view this content.
    Directions for running the routine(s) just supplied

    Copy the code to the clipboard

    Press ALT + F11 to open the Visual Basic Editor.

    Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

    Select “Module” from the Insert menu

    Type "Option Explicit" then paste the code under it

    And, you should be ready to go

    With the cursor between Sub and End Sub press F5 (F8 to Single Step)

    OR

    Press ALT + Q to close the code window.

    Press ALT + F8 then double click on the macro name

+ 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. Dynamic Range Column Chart w/ Dynamic Avg Line (Excel 2007)
    By BrokenBiker in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-04-2012, 11:40 AM
  2. Fill Dynamic Range From Dynamic Source Range
    By goss in forum Excel General
    Replies: 2
    Last Post: 03-06-2012, 12:05 PM
  3. Determine if range has NO Blank Cells without looping through each cell in range
    By Excelenator in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-04-2006, 01:35 AM
  4. Replies: 2
    Last Post: 02-02-2006, 04:10 PM
  5. [SOLVED] select dynamic range with dynamic start point
    By Juli in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-30-2005, 08:05 PM

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