+ Reply to Thread
Results 1 to 3 of 3

VBA to find cell value in column and count occurances in same row within date range

  1. #1
    Registered User
    Join Date
    09-18-2008
    Location
    UK
    Posts
    2

    Question VBA to find cell value in column and count occurances in same row within date range

    Hi,

    I would like to design a macro that can find a value within a cell in a colunm and copy a value in the row then count occurances of that value next time a match in the colunm is found on a different row, but also do this only if the date range in the row is within a specified range. So a multi-critieria search and copy operation.

    To explain a little better here is a simplified example of my spread sheet that is in input for the macro.

    A B C D E
    1 PART NO. date in date out comp1 comp2
    2 12-3 01-12-13 05-12-13 R1
    3 00-1 01-12-13 05-12-13 X1 Z1
    4 12-3 03-12-13 06-12-13 R1 C3
    5 00-2 05-12-13 06-12-13 L2
    6 00-2 07-12-13 10-12-13 L3
    7 12-3 05-01-14 08-01-14 R2 C3
    8 00-1 06-01-14 10-01-14 X1 Z1
    9 00-1 06-01-14 10-01-14 X2 Z2
    10 12-3 10-01-14 15-01-14 C3 R1


    *some component fields will be blank in normal use.

    So what I want to do is to search by "PART NO." within a specific date range and count the occurances of components, and output something like this.

    So for date range from 01-01-14 >> to 01-02-14 serching part "12-3" the table on a new sheet would look something like this.

    A B
    1 12-3
    2 R1 1
    3 R2 1
    4 C3 2


    Column A lists the coponents found on the same row as the seach critria, in this example "12-3".
    Column B lists the number of occurances of the components. This search is limited to a date range input by the user.

    So what I can see is the components replaced and the amount of times replaced on a specific item over a fixed time.

    *It is difficult to describe this problem without showing the macro output, but this is just an example for the purpose of explanation.

    I know a bit of VBA programming and initially I started with an input box that asks for the part number and limit dates. Any help is greatly appreciated. I know someone can probably solve this in a 100th of the time it would take me.

    P.S I couldn't attach the .xlsx file for some reason.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: VBA to find cell value in column and count occurances in same row within date range

    You should be able to solve this using the countifs function which is available in Versions 2007-2013. You don't indicate your Excel version.

    Here is a link to syntax.

    http://www.techonthenet.com/excel/formulas/countifs.php
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    09-18-2008
    Location
    UK
    Posts
    2

    Re: VBA to find cell value in column and count occurances in same row within date range

    I'm already using countifs to count components per part, I haven't tied dates into it yet, however my formulas are so long and so many that they slow down excel everytime you enter a value into a cell covered by the countifs

    I'm using Excel 2007.

    So I thought a macro approach might help get around that.


    Please Login or Register  to view this content.
    In this case, q_1 is a cell name of a cell containing "Q1". the formula searches 12 columns, 8 in the first sheet and 4 in the 2nd, then adds all the countifs. hence it's lenght. A load of these slow down excel a bit :P

+ 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. Replies: 5
    Last Post: 05-11-2012, 03:38 AM
  2. count date occurances in range of dates...
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 04:05 AM
  3. count date occurances in range of dates...
    By Alex in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  4. [SOLVED] count date occurances in range of dates...
    By Alex in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  5. [SOLVED] count date occurances in range of dates...
    By Alex in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 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