Überprüfen Sie jede Zelle in einem Bereich für eine bestimmte characterfolge?

Ich möchte einen VBA-Code, mit dem ich jede Zelle in einem Bereich mit einem bestimmten Text überprüfen kann?

EG: for each cell in range (a:a) if value of cell = "specific text" do this else do that 

*

Wie macht man das in VBA Excel?

Solutions Collecting From Web of "Überprüfen Sie jede Zelle in einem Bereich für eine bestimmte characterfolge?"

Hier gehst du, aber bitte versuche zuerst auf Google zu finden

 Sub eachCell() Dim c As Range For Each c In Range("A1:D21") If (c.Value = "mytext") Then 'if value of cell = "specific text" c.Value = "other text" 'do this Else c.Value = "other text 2" 'do that End If Next c End Sub 

Die Verwendung einer Suchschleife ist schneller als das Betrachten jeder Zelle

 Sub Sample_Find() Dim rng1 As Range Dim rng2 As Range Dim bCell As Range Dim ws As Worksheet Dim SearchString As String Dim FoundAt As String Set ws = Worksheets(1) Set rng1 = ws.Columns(1) SearchString = "specific text" Set rng2 = rng1.Find(SearchString, , xlValues, xlWhole) If Not rng2 Is Nothing Then Set bCell = rng2 FoundAt = rng2.Address MsgBox "do something here " & FoundAt Do Set rng2 = rng1.FindNext(After:=rng2) If Not rng2 Is Nothing Then If rng2.Address = bCell.Address Then Exit Do FoundAt = FoundAt & ", " & rng2.Address MsgBox "do something here " & rng2.Address Else Exit Do End If Loop Else MsgBox SearchString & " not Found" Exit Sub End If MsgBox "The Search String has been found these locations: " & FoundAt Exit Sub End Sub 

Eine weitere Option zum Beantworten Ihres Beitrags mithilfe des AutoFilter .

Code

 Option Explicit Sub Test_AutoFilter() Dim ws As Worksheet Dim SearchString As String Dim Rng As Range Dim VisRng As Range Dim c As Range Set ws = Worksheets(1) Set Rng = ws.Columns(1) SearchString = "specific text" Rng.AutoFilter Rng.AutoFilter Field:=1, Criteria1:=SearchString ' set another range to only visible cells after the Filter was applied Set VisRng = ws.Range(Cells(1, 1), Cells(1, 1).End(xlDown)).SpecialCells(xlCellTypeVisible) If Not VisRng Is Nothing Then ' Option 1: show every cell that a SearchString was found For Each c In VisRng MsgBox "String match of " & SearchString & " found as cell " & c.Address Next c ' Option 2: show all the cells that SearchString was found (in 1 message) MsgBox "String match of " & SearchString & " found as cells " & VisRng.Address End If End Sub