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

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
Asked:
ceneiqe
  • 2
1 Solution
 
SteveCommented:
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
0
 
ceneiqeAuthor 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
 
SteveCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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