+ Reply to Thread
Results 1 to 5 of 5

Count Cells Between Two Values

  1. #1
    Forum Contributor
    Join Date
    11-21-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    132

    Smile Count Cells Between Two Values

    I have a column (AS) with several plus signs (+) located down it. Between each plus sign are blank cells. The number of blank cells are always random. For example, the column may look like this:

    ROW 14 + LOOP 1 start
    ROW 15 BLANK
    ROW 16 BLANK
    ROW 17 BLANK
    ROW 18 + LOOP 2 start
    ROW 19 BLANK
    ROW 20 BLANK
    ROW 21 + LOOP 3 start
    ROW 22 BLANK
    ROW 23 BLANK
    ROW 24 BLANK
    ROW 25 BLANK
    ROW 26 + LOOP 4 start…etc.

    I then created two variables: fr, and lr (first row, last row). With these two variables I then tried to create a loop statement that sets y = to the difference and then changes based on the next loop.

    For example, for the first loop fr would be equal to row 14 and lr would be equal to row 18. Therefore, (lr -fr) – 1 = 3 which is the number of blank cells I’m looking for. Then it would go onto the second loop where fr would now equal row 18 and lr would equal row 21. Therefore, (lr - fr)-1 = 2. So on and so forth until reaching the last row.

    If I knew the difference for each loop I think I could make the rest of my code work. Any help would be appreciated. Thank you.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    2013, 2016
    Posts
    12,938

    Re: Count Cells Between Two Values

    I have tested this on your data. You were a bit vague on your overall problem so I don't know what you want to do with any of this information as you loop through it.
    Please Login or Register  to view this content.
    Jeff

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.

    Making the world a better place one fret at a time | | |·| |·| |·| |·| | |:| | |·| |·|

    If someone helped you please click on the star icon at the bottom of their post
    If your problem is solved please go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

  3. #3
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    Posts
    2,256

    Re: Count Cells Between Two Values

    Like Jeff alluded to, it makes things easier if we know what needs to be done with the empty cells.
    In this code, they are selected and counted, as you asked for.
    Slightly shorter code.
    Please Login or Register  to view this content.
    Last edited by jolivanes; 06-14-2018 at 10:49 PM.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    12,699

    Re: Count Cells Between Two Values

    Quote Originally Posted by Neuk View Post
    I have a column (AS) with several plus signs (+) located down it. Between each plus sign are blank cells. The number of blank cells are always random
    Do you just want to get blanks?
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    11-21-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    132

    Re: Count Cells Between Two Values

    Quote Originally Posted by 6StringJazzer View Post
    I have tested this on your data. You were a bit vague on your overall problem so I don't know what you want to do with any of this information as you loop through it.
    Please Login or Register  to view this content.
    6StringJazzer,

    You hit on the head. This is exactly what I was looking for. I just needed something to define the x and y-axis for me, which you definitely did. Here's your code with a little manipulation for my purposes:


    Please Login or Register  to view this content.
    With the x and y axis now defined I can total several columns on the x axis, using the y axis to determine how far down I need the numbers totaled. Thank you very much!
    Last edited by Neuk; 06-15-2018 at 09:47 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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