(Solved) – how to count occurrences of string in a range?

  • by
(solved)-–-how-to-count-occurrences-of-string-in-a-range?

I am trying to find all cells in column H that include the string “SCA” (anywhere in cell), and change the column/ cell to the left (-2) in the corresponding row to “Scatter”.
I want to just run the while loop for the number of cells that have “SCA” in column H, so I am trying to count them.
The code I have returns 0 as count. what am I doing wrong? have any other suggestions for how to do this?


Columns("H:H").Select
    Selection.Find(What:="SCA", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Offset(0, -2) = "SCATTER"
    x = 1
    Count = WorksheetFunction.CountIf(Range("H:H"), "SCA")
    MsgBox Count
    While x < Count
    Selection.FindNext(After:=ActiveCell).Offset(0, -2) = "SCATTER"
    Wend

Leave a Reply

Your email address will not be published. Required fields are marked *