Suchen und Ersetzen ein Bereich mehrere Begriffe

Forumthread: Suchen und Ersetzen ein Bereich mehrere Begriffe

14.11.2016 08:51:40

Torsten

Hallo,
Ich habe in meiner Excel Tabelle einen Bereich z.b. W3 bis AC34,
in jedem dieser Zellen steht entweder yes oder no.
Num möchte ich in meinem VBA Code wenn jemand versehentlich dort ein "j", "ja" oder nur ein "y" einträgt dies automatisch in ein "yes" ändern.
Das gleiche bei "n" oder nein", dort soll es in ein "no" geändert werden.
Ich bin mir auch nicht sicher inwieweit die unterschiedlichen Schreibweisen von "nein" (NeIN, neIn, NEin) ein Rolle spielen können.
Es gibt in dieser Tabelle auch noch Bereiche in denen ich es genau andersherum ändern muß, deshalb kam ich ja auf die Idee das zu prüfen
Hat da jemand eine Ide für mich?
Ich habe zwar Grundkenntnisse in VBA allerdings hier scheitere ich, habe keine vernüftige Lösung bislang gefunden.
Vielen Dank im vorraus.

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Suchen und Ersetzen ein Bereich mehrere Begriffe

14.11.2016 09:05:55

ransi

Hallo,
Schau mal ob du dies brauchen kannst:

' **********************************************************************
' Modul: Tabelle1 Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim zelle As Range
    Dim bolappEvnts
    On Error GoTo errorhandler
    bolappEvnts = Application.EnableEvents
    Application.EnableEvents = False
    For Each zelle In Intersect(Target, Range("W3:AC34"))
        Select Case LCase(zelle.Value)
            Case "j", "ja", "y"
                zelle.Value = "yes"
            Case "n", "nein"
                zelle.Value = "no"
        End Select
    Next
    errorhandler:
    Application.EnableEvents = bolappEvnts
End Sub

ransi

AW: Suchen und Ersetzen ein Bereich mehrere Begriffe

14.11.2016 09:07:42

Werner

Hallo Torsten,
warum verhinderst du nicht einfach Fehleingaben? Dann sparst du dir die nachträgliche Überprüfung ob richtige Eingaben gemacht wurden, bzw. die Änderung der Fehleingaben.
Ich würde da eine Gültigkeitsprüfung auf den entsprechenden Bereich legen.
Bereich markieren - Daten - Datenüberprüfung - Zulassen: Liste - Quelle: yes;no
Dann kannst du noch eine Eingabemeldung anlegen: Wird angezeigt wenn eine Zelle des Bereiches ausgewählt wird
Und eine entsprechende Fehlermeldung bei Flascheintrag kannst du auch noch anlegen.
Gruß Werner

AW: Suchen und Ersetzen ein Bereich mehrere Begriffe

14.11.2016 11:25:14

Torsten

Hallo ransi,
erst einmal vielen Dank für den Denkanstoß.
Ich brauche das jedoch nicht als Change Ereignis sonder in meinem bestehenden VBA Code (Modul) soll die Prüfung passieren.
Jetzt versuche ich diesen Teil:
For Each zelle In Intersect(Target, Range("W3:AC34"))
Select Case LCase(zelle.Value)
so umzubauen das er auch alle Zeilen durchsucht, bislang ohne Erfolg :-))
Den Bereich habe ich vorher definiert:
Dim rngzellenJN, rngBereichJN As Range
Dim bolappEvnts
Dim lonZeileJN As Long
lonZeileJN = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set rngBereichJN = Range("W3:AC" & lonZeileJN)
On Error GoTo errorhandler
bolappEvnts = Application.EnableEvents
Application.EnableEvents = False
For Each rngzellenJN In rngBereichJN(Range(rngBereichJN))
Select Case LCase(rngzellenJN.Value)
Case "j", "ja", "y"
rngzellenJN.Value = "yes"
Case "n", "nein"
rngzellenJN.Value = "no"
End Select
Next
errorhandler:
Application.EnableEvents = bolappEvnts

AW: Suchen und Ersetzen ein Bereich mehrere Begriffe

14.11.2016 11:34:54

Torsten

Ich glaube das ich es gefunden habe, Danke:
Dim rngzellenJN, rngBereichJN As Range
Dim bolappEvnts
Dim lonZeileJN As Long
lonZeileJN = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set rngBereichJN = Range("W3:AC" & lonZeileJN)
On Error GoTo errorhandler
bolappEvnts = Application.EnableEvents
Application.EnableEvents = False
For Each rngzellenJN In rngBereichJN
Select Case LCase(rngzellenJN.Value)
Case "j", "ja", "y"
rngzellenJN.Value = "yes"
Case "n", "nein"
rngzellenJN.Value = "no"
End Select
Next
errorhandler:
Application.EnableEvents = bolappEvnts

Infobox / Tutorial

Suchen und Ersetzen mehrerer Begriffe in Excel mit VBA

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Öffne Excel und aktiviere den VBA-Editor: Drücke ALT + F11, um den VBA-Editor zu öffnen.
Füge ein neues Modul hinzu: Klicke mit der rechten Maustaste auf VBAProject (DeineDatei.xlsx), wähle Einfügen und dann Modul.

Kopiere den folgenden VBA-Code in das Modul:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim zelle As Range
    Dim bolappEvnts As Boolean

    On Error GoTo errorhandler
    bolappEvnts = Application.EnableEvents
    Application.EnableEvents = False

    For Each zelle In Intersect(Target, Range("W3:AC34"))
        Select Case LCase(zelle.Value)
            Case "j", "ja", "y"
                zelle.Value = "yes"
            Case "n", "nein"
                zelle.Value = "no"
        End Select
    Next zelle

errorhandler:
    Application.EnableEvents = bolappEvnts
End Sub

Schließe den VBA-Editor und kehre zu deiner Excel-Arbeitsmappe zurück.
Teste die Funktion: Gib in den Zellen von W3 bis AC34 die Begriffe "j", "ja", "y", "n" oder "nein" ein und beobachte, wie sie automatisch in "yes" oder "no" umgewandelt werden.

Häufige Fehler und Lösungen

Fehler beim Speichern des Codes: Stelle sicher, dass du die Datei im .xlsm Format speicherst, um VBA-Makros zu verwenden.
Zellen reagieren nicht auf Eingaben: Überprüfe, ob das Makro aktiviert ist und ob du den richtigen Bereich definiert hast.
Fallunterschiede werden nicht erkannt: Der Code verwendet die Funktion LCase, um Groß- und Kleinschreibung zu ignorieren.

Alternative Methoden

Datenüberprüfung: Du kannst auch eine Gültigkeitsprüfung einrichten, um Eingabefehler zu vermeiden:
1. Markiere den gewünschten Bereich.
2. Gehe zu Daten -> Datenüberprüfung.
3. Wähle Liste und gib yes,no als Quelle an.
Formeln: Mit der Funktion WENN kannst du die Begriffe in einer benachbarten Zelle umwandeln:
```
=WENN(ODER(A1="j"; A1="ja"; A1="y"); "yes"; WENN(ODER(A1="n"; A1="nein"); "no"; A1))
```

Praktische Beispiele

Beispiel 1: Wenn in Zelle W3 "j" eingegeben wird, verwandelt sich das in "yes".
Beispiel 2: Wenn in Zelle W4 "n" eingegeben wird, wird dies zu "no".

Diese einfachen Beispiele zeigen, wie du mit VBA mehrere Werte gleichzeitig suchen und ersetzen kannst.

Tipps für Profis

Erweiterung des Codes: Du kannst den Code erweitern, um weitere Begriffe zu ersetzen, indem du weitere Case-Anweisungen hinzufügst.
Verwendung von Arrays: Für mehr Effizienz kannst du auch Arrays nutzen, um mehrere Begriffe gleichzeitig zu suchen und zu ersetzen.

FAQ: Häufige Fragen

1. Kann ich den Code auch für andere Bereiche verwenden? Ja, ändere einfach den Bereich Range("W3:AC34") im Code in den gewünschten Bereich.

2. Funktioniert dieser Code auch in Excel Online? Leider ist VBA in Excel Online nicht verfügbar. Du musst Excel Desktop verwenden.

3. Wie kann ich das Suchen und Ersetzen mehrerer Werte ohne VBA durchführen? Du kannst die Datenüberprüfung oder die WENN-Formel verwenden, um Eingabefehler zu vermeiden oder zu korrigieren.

4. Was tun, wenn der Code nicht funktioniert? Überprüfe, ob das Makro aktiviert ist und ob der richtige Bereich ausgewählt wurde. Debugge den Code gegebenenfalls.

Beliebteste Forumthreads (12 Monate)

Suchen und Ersetzen ein Bereich mehrere Begriffe

Forumthread: Suchen und Ersetzen ein Bereich mehrere Begriffe

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Suchen und Ersetzen mehrerer Begriffe in Excel mit VBA

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Suchen und Ersetzen ein Bereich mehrere Begriffe

Forumthread: Suchen und Ersetzen ein Bereich mehrere Begriffe

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Suchen und Ersetzen mehrerer Begriffe in Excel mit VBA

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread