none
Counting Occurances of Text in a Range of Cells RRS feed

  • Question

  • This code, which I found here http://support.microsoft.com/kb/89794 seems to work fine.  However, when I pass in the elements of a dynamic array instead of Target, it doesn't work.  For instance, if the elements of the array (selected by user via input box) are B, C, D, E, F, G it will not count these letters in the cells even though they are there.  It does return a count, but I can't tell what it is counting (definitely not the letters though).  Does anyone know why this might be? 

    Dim Count As Integer
    Dim Target As String
    Dim Cell As Object
    Dim N As Integer

    Sub Target_Count()
       Count = 0
       Target = InputBox("character(s) to find?")
       If Target = "" Then GoTo Done
          For Each Cell In Selection
             N = InStr(1, cell.Value, target)
             While N <> 0
                Count = count + 1
                N = InStr(n + 1, cell.Value, target)
             Wend
          Next Cell
       MsgBox count & " Occurrences of " & target
    Done:
    End Sub

    Thank you,

    Lucian

    Monday, November 21, 2011 9:44 PM

Answers

  • As coded, VBA considers "B, C, D, E, F, G" as a single string with multiple characters rather than an array of single character strings.  You need to create the array like:

     

    TargetArray = SPLIT(Target,", ") and then loop on the elements of TargetArray


    gsnu201111
    Monday, November 21, 2011 11:27 PM
    Moderator