CODEWITHSUNDEEP
vbaexcel
Mcodewick
Mcodewick

Reputation: 21

Count multiple values in Same Cell, within whole Row in Excel

I'd like to create a formula to look at a column in excel and count if there is an entry, such as D48 for example. So it will go through and count all the "D48" entries throughout the column, even if it is separated by commas. So a cell containing "D48, D48" or "D48, D48, D48" will all be counted.

I have a working formula right now, but it takes blank cells and assigns a value of -1. How can I avoid this? Is there a more efficient way to write this formula?

Function CountText(sLookFor As String, rSearchRange As Range) As Long

Dim cel As Range

For Each cel In rSearchRange
    CountText = CountText + UBound(Split(cel.Value, sLookFor))
Next cel

End Function

but here's the code that worked based on responses below (problem solved!!!)

Function CountText(sLookFor As String, rSearchRange As Range) As Long
    Dim cel As Range

    For Each cel In rSearchRange

    If cel = "" Then

    Else
        CountText = CountText + UBound(Split(cel.Value, sLookFor))
    End If
Next cel

End Function

Upvotes: 0

Views: 289

Answers (2)

Karl Kristjansson
Karl Kristjansson

Reputation: 328

How about:

For Each cel In rSearchRange
    if cel = "" then
        next cel
    else
        CountText = CountText + UBound(Split(cel.Value, sLookFor))
    end if
Next cel

or 

For Each cel In rSearchRange
    if cel <> "" then
        CountText = CountText + UBound(Split(cel.Value, sLookFor))
    end if
Next cel

Upvotes: 0

user4039065
user4039065

Reputation:

Track the number of instances of your 'delimiter' by retrieving the length of the string minus the retrieving the length of the string without the delimiliter divided by the length of the delimiter. 

Function CountText(sLookFor As String, rSearchRange As Range) As Long
    Dim cel As Range
    For Each cel In rSearchRange
        CountText = (Len(cel.Value) - Len(Replace(cel.Value, sLookFor, vbNullString))) / Len(sLookFor)
    Next cel
End Function

The replace function is currently case-sensitive but can be altered to case-insensitive.

Upvotes: 0

Related Questions