Dynamically select ranges between two keywords in a single column

Hello everyone I'am new to excel so any help is appreciated. If anyone need help in Safety and Health field I would be glad to help (Thats is my field of expertise).

If you have one column let's say ''A'' and in that column a couple of headings (a bolded word in a single cell, same column). In between these headings are cells with text. How to find number of cells in a range between two headings (keywords)? Let's say there are four headings. This means that there will be two separate ranges which will, when counted for number of cells, give two different numbers. The name of the Headings (keywords) is fixed but the address changes based on number of cells in between.

Example- Headings are: Apple, Orange, Pear, Lemon.

Day1:

A1 has heading Apple

A2 Some text

A3 Some text

A4 has heading Orange

A5 Some text

A6 Some text

A7 Some text

A8 has heading Pear

A9 Some text

A10 has heading Lemon

A6 Some text

A7 Some text

Result: B1 2 (Two apples)
B2 3 (Three Orange)
B3 1 (One Pear)
B4 2 (Lemon)

Day2:

A1 has heading Apple

A2 Some text

A3 has heading Orange

A4 Some text

A5 Some text

A6 has heading Pear

A7 Some text

A8 has heading Lemon

A9 Some text

Result: B1 1 (Two apples)
B2 2 (Three Orange)
B3 1 (One Pear)
B4 1 (Lemon)

Day3:

A1 has heading Apple

A2 Some text

A3 Some text

A4 has heading Pear

A5 Some text

A6 has heading Lemon

A7 Some text

Result: B1 2 (Two apples)
B2 0
B3 1 (One Pear)
B4 1 (Lemon)

So heading names are fixed and one day you can have some headings missing from the column.

I tryed some thing. It does work correct only for first Apple and Orange but if it should count between Apple and Pear it does not worl. So hope somebody can help me please

Here is the code:

Please Login or Register  to view this content.

Try

Please Login or Register  to view this content.

I think this does what you want - if not, please upload a sample workbook

Please Login or Register  to view this content.

Jindon, StephenR thanks so much to both of you . Both solution works. You are the best.

I dont understand how does this work, if anyone could explain in short how this code work would be greatly appreciated (to try to learn some of it).

Best regards to both gurus

Please Login or Register  to view this content.

Glad it worked. My code uses the Find method to return the number of the row on which each item is found and then works out the difference between the row numbers to return the value that you are after. The last few lines with Offsets are just positioning the results in columns B and C. Hope that helps.

I just noticed that empty cells are counted. Is there a way to modify the code so is does not count empty cells?
Best regards to both guru's

Please Login or Register  to view this content.

Thanks to both of guru's. True proffesionals.

Tanti cari saluti

I have another problem. How to check if cell has a picture (pictures floats over cells but are not dead on center) in a single colum (example "L" column), and if TRUE assign to that cell (or next column same row) a number (example number "1"? Is this possible?

Example:

Cell "L1" has a picture "L1" is assinged number "1"
Cell "L2" does not have a picture "L2" remains empty
Cell "L3" does not have a picture "L3" remains empty
Cell "L4" has a picture "L4" is assinged number "1"
Cell "L5" does not have a picture "L5" remains empty

I would use the code provided by the guru Jindon (once again thanks) to count the number of cell that has a number "1".

Best regard to anyone who can help

Upload a workbook clearly showing what you have and what exactly you want for the result.

Here is the workbook:



Please run the macro button Stat.Analysis.

I need to have for each OU (operational unit) a number of "Unsolved findings" and "Overdue findings" in table (sheet2, column "C") and (sheet2 , column "F") respectively.

Example:

Case1 - "Unsolved findings" :
Sheet1 cell "K9" has a DATE (that is Notdue or not Expired) and sheet1 cell "L9" does not have a picture.

Case2 - "Overdue findings":
Sheet1 cell "K9" has a DATE (that is Overdue or Expired) and sheet1 cell "L10" does not have a picture.

I thaught assigning a number "1" to a cell which contains picture a can be used for this. Maybe there is easier way.

Is there anyone who can help me?