0

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
byte me
  • 770
  • 6
  • 13
Goldaaron18
  • 11
  • 5
  • 2
    **1.** You can achieve the same using Excel Formula **2.** If you want to use VBA then use `.Find` and `.FindNext` to search for "SCA". Also handle the situation when it is not found. You may want to see [.Find and .FindNext](http://www.siddharthrout.com/index.php/2018/01/05/find-and-findnext-in-excel-vba/) **3.** Avoid the use of `.Select/Selection` etc. You may want to see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Siddharth Rout Dec 26 '19 at 14:30
  • Didn't I use Find and find next? how do I count the occurrences? .findnext seems to find the first value after it finds the last so how do you make it stop after finding last cell with "sca"? – Goldaaron18 Dec 26 '19 at 14:37
  • With a formula, you can put this in a column where you want "Scatter" to show when that same row has "SCA" anywhere: `=IF(COUNTIF(H1,"*SCA*")>0,"Scatter","")` – BruceWayne Dec 26 '19 at 14:57
  • Yes you did use `.Find` and `.FindNext` but not correctly. Did you see the link that I shared above? – Siddharth Rout Dec 26 '19 at 14:59
  • looks like the first example here works well, thanks for help though https://learn.microsoft.com/en-us/office/vba/api/excel.range.findnext – Goldaaron18 Dec 26 '19 at 15:03
  • lol.. I am speechless... – Siddharth Rout Dec 26 '19 at 15:10

0 Answers0