Counting Groups Of Cells

[FALLSILENT]

Hi,
I'm using a spreadsheet for utilization. I use different letters for different meanings. One of them is "R" I'm trying to find a way that it will count how many "R's are in a row, but only count groups.


[TABLE="width: 1067"]
<colgroup><col width="48" span="4"><col width="59"><col width="48" span="17"></colgroup><tbody>[TR]
[TD="class: xl65, width: 48"]R[/TD]
[TD="class: xl64, width: 48"]R[/TD]
[TD="class: xl64, width: 48"]R[/TD]
[TD="class: xl64, width: 48"]L[/TD]
[TD="class: xl64, width: 59"]L[/TD]
[TD="class: xl64, width: 48"]L[/TD]
[TD="class: xl64, width: 48"]L[/TD]
[TD="class: xl64, width: 48"]R[/TD]
[TD="class: xl64, width: 48"]R[/TD]
[TD="class: xl64, width: 48"]R[/TD]
[TD="class: xl65, width: 48"]R[/TD]
[TD="class: xl65, width: 48"]R[/TD]
[TD="class: xl65, width: 48"]R[/TD]
[TD="class: xl65, width: 48"]R[/TD]
[TD="class: xl65, width: 48"]R[/TD]
[TD="class: xl65, width: 48"]R[/TD]
[TD="class: xl65, width: 48"]L[/TD]
[TD="class: xl65, width: 48"]L[/TD]
[TD="class: xl65, width: 48"]L
[/TD]
[TD="class: xl65, width: 48"]R
[/TD]
[TD="class: xl65, width: 48"]L[/TD]
[TD="class: xl65, width: 48"]L[/TD]
[/TR]
</tbody>[/TABLE]


And At The End Count "R" as 3. I Know How To Count All Of Them, But I Want To Only Count The Groups.

Thanks In Advance!

 

[Takae]

Hope this helps.

Sub test()
Dim rng As Range, c
Dim cnt As Long, cnt1 As Long, buf As String
buf = "L"
Set rng = Range(Range("A2"), cells(2, Columns.count).End(xlToLeft))
For Each c In rng
   buf = buf & c.Value
Next
cnt = Len(buf)
cnt1 = Len(WorksheetFunction.Substitute(buf, "LR", "$"))
MsgBox cnt - cnt1
End Sub

 

[jtakw]

Hi,

If you want a formula solution and don't mind a helper row:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	R	R	R	L	L	L	L	R	R	R	R	R	R	R	R	R	L	L	L	R	L	L	Count of R Series
2			R				L									R			L	R		L	3
Sheet53

Cell Formulas
Range	Formula
A2	=IF(A1=B1,"",A1)
W2	=COUNTIF(A2:V2,"R")

A2 formula copied across to V2.

 

[Stephen_IV]

Just a question. Trying to learn. Why substitute "LR" with "$". Trying to find out what "$" means?

Len(WorksheetFunction.Substitute(buf, "LR", "$"))

 

[FALLSILENT]

Just a question. Trying to learn. Why substitute "LR" with "$". Trying to find out what "$" means?

Len(WorksheetFunction.Substitute(buf, "LR", "$"))

Yes! I've wondered that, but haven't had the time to sit down and read up that far yet.

 

[Takae]

There is no special reason.
It just change the "LR" to "$" then counts the difference in number of letters.