counting occurences of a string in combination of another string in the same row

Question

I'm trying to count occurrences of a string but only if another string in the row is eg. "Completed"

I get Error 9 Subscript Out of Range in row 15, but I just can't figure it out. I search for the second string in column 11 which should be inside the array.

Sub instancesWithStatus()

    Dim catchPhrase As String

    Dim completionStatus As String

    Dim lastRow As Long

    Dim dataArray As Variant

    Dim i As Long

   

    catchPhrase = InputBox("What is the string to match?")

    completionStatus = InputBox("Search for Completed, In Progress or Not Started")

    lastRow = Cells(Rows.Count, 5).End(xlUp).Row

    dataArray = Range(Cells(1, 5), Cells(lastRow, 11)).Value

   

    For i = 1 To lastRow

        If dataArray(i, 5) = catchPhrase And dataArray(i, 11) = completionStatus Then

            Dim catchPhraseCount As Long

            catchPhraseCount = catchPhraseCount + 1

        End If

  Next i

 

  Debug.Print cnt

End Sub

I'm not sure what to even do. I declare the range of the array to include column 11 but it somehow doesn't work.

Answer 1

• dataArray is a 2D array. Its index start from 1.
• Dim catchPhraseCount As Long initializes a variable (set value to 0). It is moved before For loop. Otherwise the variable will be reset again and again.
• Debug.Print catchPhraseCount should use catchPhraseCount instead of cnt (I guess it's just a typo).
• Range object qualified with sheet object makes the code more reliable.

Microsoft documentation:

InputBox function

Range.End property (Excel)

Option Explicit

Sub instancesWithStatus()
    Dim catchPhrase As String
    Dim completionStatus As String
    Dim lastRow As Long
    Dim dataArray As Variant
    Dim i As Long
    Dim catchPhraseCount As Long
    Dim oSht As Worksheet
    Set oSht = ActiveSheet ' modify as needed
    catchPhrase = InputBox("What is the string to match?")
    completionStatus = InputBox("Search for Completed, In Progress or Not Started")
    lastRow = oSht.Cells(oSht.Rows.Count, 5).End(xlUp).Row
    dataArray = Range(oSht.Cells(1, 5), oSht.Cells(lastRow, 11)).Value
    For i = 1 To lastRow
        If dataArray(i, 1) = catchPhrase And dataArray(i, 7) = completionStatus Then
            catchPhraseCount = catchPhraseCount + 1
        End If
    Next i
    Debug.Print catchPhraseCount
End Sub