CODEWITHSUNDEEP
excelvba
bandika
bandika

Reputation: 15

Counting the matching substrings in range

I am working on a workbook in which I need to count how many times the "St/" substring is present in a Range (Column Q). Note: I am interested in all the occurrences, not just the number of cells in which the substring is present.
Here is the code I am trying to work with (based on the comment of Santhosh Divakar - https://stackoverflow.com/a/23357807/12536295), but I receive a runtime error (13) when running it. What am I missing / doing wrong?

Dim lastrow, q as Integer
lastrow = Range("A1").End(xlToRight).End(xlDown).Row
With Application
q = .SumProduct((Len(Range("Q1:Q" & lastrow)) - Len(.Substitute(Range("Q1:Q" & lastrow), "St/", ""))) / Len("St/"))
End With

Upvotes: 1

Views: 116

Answers (5)

Алексей Р
Алексей Р

Reputation: 7627

The TextJoin() function in Excel 2019+ is used:

Sub CalcSt()
    Const WHAT = "St/": Dim joined As String
    joined = WorksheetFunction.TextJoin("|", True, Columns("Q"))
    Debug.Print (Len(joined) - Len(Replace(joined, WHAT, ""))) / Len(WHAT)
End Sub

Upvotes: 1

T.M.
T.M.

Reputation: 9948

Using ArrayToText() function

a) If you dispose of Excel version MS365 you can shorten a prior string building by evaluating the tabular ARRAYTOTEXT() formula to get a joined string of all rows at once (complementing @Foxfire 's valid solution).

Note that it's necessary to insert the range address as string; in order to fully qualify the range reference I use an additional External:=True argument.

b) VBA's Split() function eventually allows to return the number of found delimiters (e.g. "St/") via UBound() function. It returns the upper boundary (i.e. the largest available subscript) for this zero-based 1-dimensional split array.

Example: If there exist eight St/ delimiters, the split array consists of nine elements; as it is zero-based the first element has index 0 and the last element gets identified by 8 which is already the wanted function result.

Function CountOccurrencies(rng As Range, Optional delim as String = "St/")
'a) get a final string (avoiding to join cells per row) 
    Dim txt As String
    txt = Evaluate("ArrayToText(" & rng.Address(False, False, External:=True) & ")")
'b) get number of delimiters 
    CountOccurrencies = UBound(Split(txt, delim))
End Function

Upvotes: 2

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

See if the code below helps you:

Public Sub TestCount()
    lastrow = Range("Q" & Rows.Count).End(xlUp).Row
    strformula = "=SUMPRODUCT(LEN(Q1:Q" & lastrow & ")-LEN(SUBSTITUTE(UPPER(Q1:Q" & lastrow & "),""/ST"","""")))/LEN(""/St"")"
    MsgBox Evaluate(strformula)
End Sub

Upvotes: 3

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11988

Not the cleanest one, but you can take all into arrays and split by St/. Size of that array would be how many coincidences you got:

Sub test()
Dim LR As Long
Dim MyText() As String
Dim i As Long
Dim q As Long

LR = Range("Q" & Rows.Count).End(xlUp).Row
ReDim Preserve MyText(1 To LR) As String

For i = 1 To LR Step 1
    MyText(i) = Range("Q" & i).Value
Next i

q = UBound(Split(Join(MyText, ""), "St/"))

Debug.Print q
Erase MyText
End Sub

enter image description here

The output i get is 8

Please, note this code is case sensitive.

Upvotes: 1

Dave Lett
Dave Lett

Reputation: 88

I think you can count the number of characters, replace your "St/" with nothing and then count the characters again and divide by len("St/"). Here's an example.

'''your existing code
Dim lCount As Long
Dim lCount_After As Long
'''set a Range to column Q
Set oRng = Range("Q1:Q" & lRow_last)
'''turn that range into a string
sValues = CStr(Join(Application.Transpose(oRng.Value2)))
lCount = Len(sValues)
lCount_After = lCount - Len(Replace(sValues, "St/", ""))
lCount_After = lCount_After / 3

Debug.Print lCount_After

Upvotes: 2

Related Questions