counting the number of cells at intervals of a string in column C

Hi I would like to count the number of cells in my data.

The cell intervals are seperated by this string "SICOM" in column C.

Can someone help me?

Thank you very much.

Can you give an example?

vb Code:

cnt = Application.WorksheetFunction.CountIf("c:c", "SICOM")

http://img.photobucket.com/albums/v1...projectmon.jpg

Thanks I'll try that out.

Here's an example..

I'm trying to calculate the number of cells from the first 'XICOOMM' to the next 'XICOOMM'.

I wanna fill up the first 2 column with primary keys for referencing.

and then the XICOOMM at ie, row 50.. to the very next XICOOMM.

the data generated only states the primary key once, somewhere above the data in column say 'E'.

Thank you!

Lets say the data is in Cell A1 to A15

Non VBA Method

=CountA(A1:A15)

The above formula will give you the number of cells which has alphanumeric values in the cells. If you want to exclude the two cells which have XICOOMM then the above formula becomes

=CountA(A1:A15) - 2

VBA Method

Application.WorksheetFunction.CountA("A1:A15") will give you the same results as above. Subtracting two will be required if you want to exclude the cells which contain XICOOMM.

Hope this is what you wanted?

hey that is close, however i need the formula to read the 'XICOOMM' text..

so from 1st XICOOMM to next (2nd XICOOMM), perform a count

from 2nd XICOOMM to the next (3rd XICOOMM; count.

Thank you

Do you also want to count the empty cells between cells which have XICOOMM?

yea no harm in that! =)

i just need to prepare the data such that it has a primary key.

the primary key is only stated once in column d~

gotta have a formula to fill in column A or B

One last question. Do you want to use VBA or Non VBA Method?

i would like to explore both~

looks like you need to use excels find method to find xicooom, then findnext to find the next occurance, calculate the rows between

Perhaps this does not help much:

http://www.vbforums.com/attachment.p...1&d=1271334292