New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Count the Number of Occurances of Any Variation of a Set of Characters within a Range of Cells in Excel

Free Excel Macros

Count the Number of Occurances of Any Variation of a Set of Characters within a Range of Cells in Excel

Postby Don » Tue Jan 31, 2012 10:29 pm

This Excel macro allows you to count how many times any variation of characters appears within a range of cells wtihin the spreadsheet.

For instance, you can use this to figure out how many cells contain the letters a, b, c, d within a range regardless of the order in which these characters appear within any cell within the range. As long as a cell in the range contains these four characters, the cell will be included in the final count.

This macro can be adapted to work in many different ways; however, it currently loops through the cells in a single column. By default, this works on column A. If you want to change that, change the A in this line

For i = 1 To Range("A" & Rows.Count).End(xlUp).Row

to the letter of the column through which you want to loop. And, change the number 1 in this line:

cellnums = Cells(i, 1).Value

to the number of the column through which you want to loop. Column A is 1; column B is 2; and so on.

To change the characters for which you want to search, change the values for these for variables to whatever you want:

one = 1
two = 2
three = 3
four = 4


If you want to check for text, make sure to surround the text with double quotation marks.


Where to install the macro: Module


Code: Select all
Sub CountVariationOccurance()

'used to output the number of occurances found in the range
instanceCount = 0

'the four characters that you want to check for within a cell
'if you want to use text here, make sure to put them within double quotes
one = 1
two = 2
three = 3
four = 4

'loop through the range
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row

    cellnums = Cells(i, 1).Value
   
    If InStr(1, cellnums, one) > 0 And InStr(1, cellnums, two) > 0 And InStr(1, cellnums, three) > 0 And InStr(1, cellnums, four) > 0 Then
        instanceCount = instanceCount + 1
    End If

Next i

'output the count
MsgBox instanceCount

End Sub
Don
Moderator
 
Posts: 733
Joined: Dec 4, 2011
Reputation: 2
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 29 guests