+ Reply to Thread
Results 1 to 9 of 9

VBA Count Records Which Match Column Heading

  1. #1
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    VBA Count Records Which Match Column Heading

    Hi, I wonder whether someone may be able to help me please.

    I'm trying to put together a script which counts the number of projects if the Project Month & Year are equal to the column header.

    To help with my explanation, I've attached a file which I hope will illustrate what I'm trying to accomplish.

    You will see there are 3 sheets:

    • The first is "In Flight Projects" which contains the raw data displaying the projects currently being worked on by staff.
    • The "Output" sheet shows my current 'Output' sheet, and
    • The last is my 'Desired' outcome.

    What I'm trying to do is as follows:

    • Search the column headings on the "Output" sheet which are dynamic, so the number of values will change, then
    • For each column heading, search column G on the "In Flight Projects" sheet and,
    • Where the date is greater than or equal to the column heading, count the project from column B on the "In Flight Projects" sheet (note the number of projects are also dynamic), and
    • Show the total number of projects in the appropriate column on the "Output" sheet

    For example you will see that in Feb 15, there were 30 projects being worked, as was the case in Mar 15.

    Three projects then had a finish date in Mar 15, so they were removed from the count in Apr 15.

    I normally have a good idea where to start, but this has really puzzled me.

    I just wondered whether someone could possibly look at this please and offer some guidance on how I may go about achieving this.

    Many thanks and the kindest regards
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA Count Records Which Match Column Heading

    Not sure I understand, so test on a copy first.

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Count Records Which Match Column Heading

    Hi @John H. Davis, thank you for taking the time to reply to my post and for putting the solution together.

    Unfortunately, the code doesn't quite have the desired affect.

    The code you kindly provided, which in itself is great, it counts the number of projects with a 'Month & Year' value which matches the column headings, whereas, I'm trying to count the number of projects which have a 'Month & Year' value which are greater than and equal to the column headings.

    Many thanks and kind regards

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA Count Records Which Match Column Heading

    Unless your desired output is wrong, I don't think this is it? Did you get your desired output with a formula?

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Count Records Which Match Column Heading

    Hi, thank you very much for coming back to me with this.

    Firstly, I wanted to offer you my apologies because my calcularions for the desired results weren't 100% accuaret, I'm sorry, but unfortunately, even taking this into account the revised script, although nearer didn't produce the desired results.

    So I've continued to work with this and got this to work with the following:

    Please Login or Register  to view this content.
    I just wondered though whether you may be able to help me with a could of issues please.

    Could you tell me please, is it possible to make both the 'Header' and 'Project Date' ranges dynamic i.e. until the first blank column and blank row instead of "C7:W7") and Do Until ActiveCell.Row > 38.

    Many thanks and kind regards
    Attached Files Attached Files
    Last edited by hobbiton73; 02-20-2014 at 04:14 AM.

  6. #6
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA Count Records Which Match Column Heading

    Hi Christ,
    Try the attached.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Count Records Which Match Column Heading

    Hi @AB33, thank you for taking the time to reply to my post and for putting the solution together which works perfectly.

    May I ask please, would it be possible for you to comment the code, so that I can learn from this?

    Many thanks and kind regards

  8. #8
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA Count Records Which Match Column Heading

    Sent you a PM

  9. #9
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Count Records Which Match Column Heading

    Hi thank you @AB33, I've picked this up and replied.

    Kind Regards

+ 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] Count number of cells in a column that do not match records in a seperate range
    By ports83 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-13-2013, 08:51 AM
  2. Count if column heading is
    By nd2828 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-09-2012, 03:08 PM
  3. [SOLVED] How to match Row Name with Column Heading and display Column Value?
    By cezondz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-06-2012, 10:38 AM
  4. Replies: 0
    Last Post: 05-17-2012, 12:59 PM
  5. Find Lowest Time, Match with Column Heading
    By nadler1744 in forum Excel General
    Replies: 1
    Last Post: 07-08-2009, 01:17 AM

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