CODEWITHSUNDEEP
excelvba
Soto
Soto

Reputation: 75

Count cells that contain certain letters (Visual Basic)

I'm trying to write a function in VBA that will count the cells that contain certain letters in a specific range. My code so far is

Function letters(list As range) As Double

    Dim limit As Integer
    limit = UBound(list.Value)

    Dim total As Double
    total = 0

    Dim i As Integer

    For i = 1 To limit
        If range(list).Offset(i, 2) = "PD" Then
        total = total + 1
        End If
    Next i

    letters = total

End Function

So my input is =letters(A1:B6) and lets say B2, B3, and B5 have the text "PD". I expect my answer to be 3 but I get a #value! error. Where am I going wrong?

Thanks in advance.

Upvotes: 0

Views: 1677

Answers (3)

user3598756
user3598756

Reputation: 29421

to answer your question

Where am I going wrong?

there are some points to be addressed:

  • you say you call your function with 

     =letters(A1:B6)

    but A1:B6 doesn't mean anything, while you have two options:

    • either you call your function passing a literal string as a valid Range address

      =letters("A1:B6")

    • or you call it passing a Range object directly

      =letters(Range("A1:B6"))

  • once you're done with the previous point you must act consistently, i.e.:

    • option 1: passing a literal string as a valid Range address

      then "A1:B6" (notice the double quotes surrounding A2:B6) is the address of your range so you have to:

      • change:

        limit = UBound(list.Value) '<--| error

        a string doesn't have a Value property

        to: 

        limit = UBound(Range(list).Value) '<--| OK

        Range("A1:B6") is a valid Range reference so you can access its Value property

      • change:

        Range(list).Offset(i, 2) '<--| this references a range as large as the one it's being called upon!

        Offset(nRows, nColumns) method will reference a range that's "shifted" from the one it's being called upon by as many rows and columns as you put in its parameters. So Offset(i,2) called upon Range("A1:B6") references Range("C2:D7") when i=1, Range("C3:D8") when i=2 and so on

        to:

        Range(list).Cells(i, 2) '<--| it references a single cell

        Cells(iRow, iColumn) method will reference the cell in the iRowth row and iColumnth column of the Range it's being called upon. So Range("A1:B6").Cells(1,2) references cell "B7"

    • option 2: passing a Range object

      since list is already a Range object (namely Range("A1:B6")), you have to:

      • change:

        If Range(list).Offset(i, 2) = "PD" Then '<--| error

        you cannot pass a Range object as the only argument of another Range object

      to:

          If list.Cells(i, 2) = "PD" Then '<--| Ok

      where you use Cells property of Range object as already discussed in option 1

  • then you may want to consider the following miscellanea:

    • declare your Function as of Long type

      since:

      • you're returning an integer value, so there's no need to use a Double type

      • the number of row can exceed some 65 k limit of an Integer type variable

    • declare Dim limit As Long

      since with

       limit = UBound(Range(list).Value) '<--| correct use of 'list' if it's a literal 'string'

      you're assigning it the actual number of rows of a Range and it could exceed the some 65k limit of an Integer type variable

all that said, I think the most effective VBA solution would be using WorksheetFunction.CountIF() function as follows:

Function letters(list As String) As Long
    letters = WorksheetFunction.CountIf(Range(list).Columns(2), "PD")
End Sub

where you'd use .Columns(n) property of Range object to reference the nth column of the Range it's being called upon

and which you could make more general by expanding its arguments list to the searched string

Function letters(list As String, myLetters As String) As Long
    letters = WorksheetFunction.CountIf(Range(list).Columns(2), myLetters)
End Sub

that you'd call like:

MsgBox letters("A1:B6", "PD")

Upvotes: 1

nightcrawler23
nightcrawler23

Reputation: 2066

If you want, you can achieve this just by using the below formula instead of using VBA.

=SUMPRODUCT(--(IFERROR(FIND("PD",A1:A25,1)>0,FALSE)))

You need to enter this as an array Formula i.e. Enter the formula and press Ctrl Shift Enter

FIND("PD",A1:A25,1) gives you the text position or an error if text is not found for each cell.

>0 converts the number to TRUE. If it an error IFERROR converts it to FALSE. Hence you get an array of TRUEs and FALSEs like {TRUE,FALSE,...}

The --({...}) converts the array to 1s and 0s. 1 for TRUE and 0 for FALSE. SUMPRODUCT adds this numeric array to give you the count.

Upvotes: 0

nightcrawler23
nightcrawler23

Reputation: 2066

You can loop through each cell in the range and use INSTR to find if the cell contains the text. Increment a counter if it does and then output it.

Function findText(myRange As Range, toFind As String)
    Dim ra As Range
    Dim rCount As Integer
    rCount = 0


    For i = 1 To myRange.Rows.Count
        If InStr(1, myRange(i, 1), toFind, vbTextCompare) Then
            rCount = rCount + 1
        End If
    Next i

    findText = rCount

End Function

This will count event those cells which have the required text anywhere in the cell. If you want to match the whole cell contents, just use 

If myRange(i, 1) = toFind Then

Also, the function works for a range in single column.

Upvotes: 1

Related Questions