• Status: Solved
• Priority: Medium
• Security: Public
• Views: 333

# 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
0
ceneiqe
• 2
1 Solution

Commented:
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
``````
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
0

Author Commented:
"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 ?
0

Commented:
There is no need to 'run' the Function will behave like other in-build Excel functions.
You just need to change the number of occurences from 3.

If you chnage it to 1 then you will effectively get a "count", this will highlight the rows with spaces in them.

Attached file will ignore spaces and work with your existing data.
Consecutives-count.xls
0

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.