asked on

Create a macro: formula to show count how many consecutive alphabet in a spreadsheet

See attached sheet.
Consecutives-count.xls

I need to show the following :

Column P to have a show how many times "A" occured consecutively for 3 times

Column Q to show how many times "A" occured consecutively for 6 times

Column R to show how many times "A" occured consecutively for more than 6 times

Steve

OK in the workbook attached is the following formula:

This allows you to select what you are looking for and how many times to triger the count...

Function ConsecutiveCount(Letter As String, Occurence As Integer, TheRange As Range) As Integer
Dim i As Long
For Each c In TheRange
    If c.Value = Letter Then
        i = i + 1
        If i >= Occurence Then ConsecutiveCount = ConsecutiveCount + 1
    Else
        i = 0
    End If
Next
End Function

Open in new window

Please note that the second "A" in about the third line had a space in it... so was in error...
maybe a find replace " " to correct this on all or changing the If c.Value = Letter Then to If c.Value like "*" & Letter & "*" Then will fix that.
Consecutives-count.xls

ceneiqe

ASKER

"Please note that the second "A" in about the third line had a space in it... so was in error..."

I didn't see any error. Consecutives-count--1-.xls

Which exact cell are you refering to ?

Is it possible to run auto by pressing alt f8 and run ?

ASKER CERTIFIED SOLUTION

Steve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial