Live-Forum - Die aktuellen Beiträge
Datum
Titel
14.10.2025 14:57:29
Wenn Datum in Ferienzeit dann Name angeben
14.10.2025 09:39:44
VBA - Excel speichern und schließen (Laufzeitfehler 91)
13.10.2025 19:31:31
VBA Formel mit Bezug auf Überschrift anstatt Spalte
13.10.2025 15:10:17
Überlappende Zeiträume über mehrere Tabellen mit leeren Zell
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1628to1632
1628to1632
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Suchen und Ersetzen mit Bedingungen in Schleife

Forumthread: Suchen und Ersetzen mit Bedingungen in Schleife

Suchen und Ersetzen mit Bedingungen in Schleife
22.06.2018 10:06:11
Rilana
Guten Morgen liebe Excelprofis,
ich habe ein für mich sehr komplexes VBA-Problem, bei dem ich mit meinen Kenntnissen (auch mit der Suche im Forum) nicht weiter komme und daher euch um Hilfe bitte:
Zum Problem:
Ich habe eine Excel Mieterliste die wie folgt aufgebaut ist:
Spalte A: Mietername
Spalte B: Angabe ob Wohnungs- oder Gewerbemieter
(Innnerhalb der Spalten können auch Leerzeilen enthalten sein.)
Durch die neue Datenschutzverordnung ist es nun notwendig, dass ich zumindest die Wohnungsmieter anonymisiere. Statt dem Namen (z.B. Max Mustermann usw.) möchte ich nun fortlaufend nummeriert 'Mieter 1', 'Mieter 2' usw. in der Spalte A stehen haben. Die Namen der Gewerbemieter sollen unverändert bleiben. Es kann jedoch sein, dass ein Mietername mehrmals in Spalte A auftaucht (da er z.B. auch noch separat einen Stellplatz angemietet hat), der soll dann zwecks Zuordnung jeweils die gleiche Mieternummer erhalten.
Als Beispiel:
vorher:
Spalte A - Spalte B
M. Mustermann - Wohnungsmieter
F. Meier - Wohnungsmieter
ABC GmbH - Gewerbemieter
C. Schuster - Wohnungsmieter
F. Meier - Wohnungsmieter
nachher:
A - B
Mieter 1 - Wohnungsmieter
Mieter 2 - Wohnungsmieter
ABC GmbH - Gewerbemieter
Mieter 3 - Wohnungsmieter
Mieter 2 - Wohnungsmieter
Von der Theorie habe ich es mir so vorgestellt:
- in einer Schleife soll für die Zellen 1 bis 500 in Spalte A geprüft werden ob ein Mieter eingetragen ist, ob es sich um einen Wohnungsmieter handelt und ob der Beginn des Mieternamens ungleich 'Mieter' ist.
- wenn dem so ist, soll der Mieter wie oben beschrieben umbenannt werden, und es muss geprüft werden, ob der Mieter in Spalte A noch einmal auftaucht und entsprechend mit gleicher Mieternummer bezeichnet werden muss.
Leider habe ich bei der Umsetzung in VBA nicht einmal einen Ansatz, so dass ich für jede Hilfe und jeden Tip sehr dankbar wäre.
Viele Grüße
Rilana
Anzeige

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Ansatz
22.06.2018 11:23:13
Fennek
Hallo,
teste diesen Code (an einer Kopie)

Sub T_1()
Dim Nm As Name
On Error Resume Next
With ThisWorkbook
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr
If Cells(i, 2) = "Mieter" Then
ll = Len(.Names(Cells(i, 1).Text).Comment)
If Err.Number  0 Then
m = m + 1
.Names.Add(Cells(i, 1), "_", False).Comment = "Mieter " & m
Debug.Print .Names.Count, .Names(Cells(i, 1).Text).Name, .Names(Cells(i, 1).Text). _
Comment
Err.Clear
End If
End If
Next i
For Each Nm In ThisWorkbook.Names
ActiveSheet.Cells.Replace Nm.Name, Nm.Comment
Next Nm
End With
End Sub
Der Code kann auch tückisch sein ...
mfg
Anzeige
AW: Ansatz
22.06.2018 12:37:51
Rilana
Hallo Fennek,
vielen Dank für die schnelle Antwort, leider hat es bei mir allerdings nicht funktioniert.
Ich habe mal die Beispieldatei hochgeladen:
https://www.herber.de/bbs/user/122247.xlsm
Vielleicht fällt Dir ja noch was anderes ein?!
Gruß Rilana
Anzeige
AW: VBA, besser als xlsx
22.06.2018 14:53:47
Fennek
Hallo,
hier der angepasste Code, der in der Bsp-Datei läuft:

Private Sub cmd_anonym_Click()
Dim Nm As Name
Dim lr As Long
Dim m As Integer
Dim Tx As String
On Error Resume Next
With ActiveWorkbook
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr
If Cells(i, 2) = "Wohnungsmieter" Then
Tx = Replace(Cells(i, 1), " ", ""): Cells(i, 1) = Tx
ll = Len(.Names(Tx).Comment)
If Err.Number  0 Then
m = m + 1
.Names.Add(Tx, "_", False).Comment = "Mieter " & m
Debug.Print .Names.Count, .Names(Tx).Name, .Names(Tx).Comment
Err.Clear
End If
End If
Next i
For Each Nm In ThisWorkbook.Names
ActiveSheet.Columns(1).Replace Nm.Name, Nm.Comment
Next Nm
End With
End Sub
Es ist besser, den Code entweder in ein anderes Workbook oder aus einer Text-Datei nur wenn er gebraucht wird einzufügen und die Datei als XLSX (also ohne Makros) zu speichern.
mfg
(lr muss asl interger oder long dimensioniert sein)
Anzeige
AW: VBA, besser als xlsx
22.06.2018 15:14:32
Rilana
Hallo Fennek,
funktioniert einwandfrei - hammer!!!
Hab 1000 Dank und ein schönes Wochenende !!!
Viele Grüße
Rilana
AW:Korrektur
22.06.2018 15:40:49
Fennek
Hallo,
hier eine kleine Verbesserung, wenn Monate später Mieter wechseln bzw Alt-Mieter etwas dazu mieten:

Private Sub cmd_anonym_Click()
Dim Nm As Name
Dim lr As Long
Dim m As Integer
Dim Tx As String
On Error Resume Next
With ActiveWorkbook
m = .Names.Count
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr
If Left(Cells(i, 1), 6)  "Mieter" And Cells(i, 2) = "Wohnungsmieter" Then
Tx = Replace(Cells(i, 1), " ", ""): Cells(i, 1) = Tx
ll = Len(.Names(Tx).Comment)
If Err.Number  0 Then
m = m + 1
.Names.Add(Tx, "_", False).Comment = "Mieter " & m
Debug.Print .Names.Count, .Names(Tx).Name, .Names(Tx).Comment
Err.Clear
Else
Cells(i, 1) = .Names(Tx).Comment
End If
End If
Next i
For Each Nm In ThisWorkbook.Names
ActiveSheet.Columns(1).Replace Nm.Name, Nm.Comment
Next Nm
End With
End Sub
Noch einmal: speichere die Daten und den VBA-Code getrennt!
mfg
Anzeige
AW: AW:Korrektur
25.06.2018 11:50:30
Rilana
Guten Morgen,
besten Dank für die Verbesserung, wobei der Fall wahrscheinlich nicht auftauchen wird.
Viele Grüße und eine schöne Woche...
;

Forumthreads zu verwandten Themen

In Spalte nach Anzahl eines Begriffs suchen?
Lauren

Hall Leute!
ich will nach einem Begriff, in meinem Fall nach "SUMs" in der Spalte C suchen und wissen wie oft es in der Spalte vorkommt.
Bei Berechnungen funktioniert es mit ZÄHLENWENN ohne Probleme aber wie erhalte ich die "absolute" Zahl?
Vielen Dank für Eure Hilfe!
Grüße
...
19.08.2008 11:08:23
Thread-ID: 1002846
5 Beiträge
Häufigkeit einer Uhrzeit aus einer Spalte
wolfgang

Hallo Leute
Ich habe eine Spalte mit einer menge Uhrzeiten und möchte die Häufigkeit einer Uhrzeit ( wie oft ist eine eintrag vorhanden)zum Beispiel zwischen 08:00 und 17:00!! Ich weiß das für euch sicher ein leichtes ist jemanden wie mir zu helfen!
Schon jetzt besten Dank für eure Mühe...
02.09.2008 15:11:51
Thread-ID: 1006816
2 Beiträge
von Datum bis Datum suchen und Werte in die Spalte
Datum

Hallo liebe Excel-Könner,
ich habe folgendes Problem und finde keine Lösung.
Ich habe in Spalte A von 2006 bis heute Datum stehen, über eine UserForm möche ich in zwei TextBoxen von bis Datum eintragen und in Spalte B in diesem Bereich den WertX.
Ich hoffe Ihr könnt mir helfen!
...
07.09.2008 09:31:20
Thread-ID: 1007931
8 Beiträge
Exceltabelle durch userform ersetzen
Peter

Hallo
ich habe im Internet einen Funktion zur Steuerberechnung gefunden und möchte diese in meine Datei ein binden.
In der Funktion sind die eingabe Felder mit den Zeilen und Spalten Bsp. B2 bezeichnet.
jetz habe ich meine Userform erweitert um Comboboxen und zur Eingabe und Label z...
28.09.2008 19:00:00
Thread-ID: 1012973
6 Beiträge
Berechnen von Werten in einer Spalte
Werten

Folgendes Problem:
In der Spalte A möchte ich wissen, wieviel unterschiedliche Werte es gibt.
Wer kann mir helfen.
Danke
Alex
05.10.2008 12:52:00
Thread-ID: 1014231
5 Beiträge
Filtern, gefilterte Daten benutzen, Suchen und Aus
Merle


Hallo alle zusammen,
ich habe ein mittelschweres Problem. Und zwar habe ich hier ein Beispiel:
https://www.herber.de/bbs/user/55986.xls
In 'Tabelle1' sind ganz viele verschiedene Waren (C17:57) hinterlegt. Ich möchte nun, wenn ich eine dieser Waren rausfilter z.B H025, dass da...
13.10.2008 15:48:00
Thread-ID: 1016087
3 Beiträge
Gleiche Eintäge in einer Spalte nacheinander finde
Malcolm_p

Hallo zusammen!
Wenn ich einen bestimmten Eintrag - per VBA - in einer Spalte finden möchte verwende ich der Regel den folgenden Code:
Dim NameSP As Range
Set NameSP = Sheets("Konten").Rows(1).Find( _
What:=Textbox1, _
Lookat:=xlWhole, _
LookIn:=xlValues)
Dim Erfdat...
15.10.2008 23:08:59
Thread-ID: 1016705
5 Beiträge
Txt - Text suchen und ersetzen VBA
Stevie

Hie zusammen,
ich hoffe mir kann hier jemand helfen, ich habe einen Code aus dem Internet (oder sogar von der Seite hier) und den muss ich leider ein bisschen anpassen. Der Code sucht in einer Textdatei nach einer bestimmten Zeichenfolge STxtA und ersetzt diesen Text durch STxtB. Soweit so...
30.10.2008 13:09:28
Thread-ID: 1020573
3 Beiträge
Spalte durchsuchen und Werte ausgeben
Sebi

Moin moin zusammen,
ich habe folgendes Problem: Ich habe in einer Spalte mehrere gleiche bzw. verschiedene Werte stehen (siehe Beispiel unten). Ich möchte nun diese Spalte durchsuchen und mir alle verschiedenen Werte in einer seperaten tabelle ausgeben lassen. Ich habe das ganze versucht mi...
05.11.2008 11:27:54
Thread-ID: 1021806
4 Beiträge
in Zeile oder Spalte Wert suchen
Lars

Hallo,
ich habe eine Zeile in der z.B. B1 B2 B3 usw. stehen.
Gibt es eine einfache Möglichkeit nach der Spalte zu suchen, in der z.B. B2 steht. Derzeit versuche ich es über eine komplizierte If-Abfrage.
Ich habe auch die Worksheetfunction "search" gefunden, weiß aber nicht, wie sie...
12.11.2008 11:12:43
Thread-ID: 1023752
7 Beiträge
Daten aus Sheet suchen und in Zelle schreiben
Robert

Hallo Excel Profis!
Ich stehe mal wieder als VBA Laie vor einem riesen Berg.
Ich habe auf "Sheet 2" eine Liste mit Quartalsdaten.
Ich suche eine Möglichkeit, wie Excel nun in diesem Sheet nach den jeweils passenden Daten eines
Quartals sucht und diese dann auf "Sheet 1" überträg...
20.11.2008 12:36:00
Thread-ID: 1026379
9 Beiträge
Werte in einer Spalte(5zeilig) suchen und löschen!
adrian

Hi,
kann mir jemand erklären warum das nicht funzt funktioniert?
liegt es daran, dass ich "SpalteR" als range deklariert habe, anstatt als string?
nur wie durchsuche ich dann den "string"?
Möchte nur folgendes:
Falls Werte in der Spalte (Bereich von nur 5 zeilen) sind, lösch...
24.11.2008 12:04:00
Thread-ID: 1027253
5 Beiträge
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Suchen und Ersetzen von Mietername mit VBA in Excel

Schritt-für-Schritt-Anleitung

  1. Öffne deine Excel-Datei mit der Mieterliste.

  2. Drücke ALT + F11, um den VBA-Editor zu öffnen.

  3. Füge ein neues Modul hinzu:

    • Klicke auf Einfügen > Modul.

  4. Kopiere den folgenden Code in das Modul:

    Private Sub cmd_anonym_Click()
   Dim Nm As Name
   Dim lr As Long
   Dim m As Integer
   Dim Tx As String
   On Error Resume Next
   With ActiveWorkbook
       m = .Names.Count
       lr = Cells(Rows.Count, 1).End(xlUp).Row
       For i = 2 To lr
           If Left(Cells(i, 1), 6) <> "Mieter" And Cells(i, 2) = "Wohnungsmieter" Then
               Tx = Replace(Cells(i, 1), " ", ""): Cells(i, 1) = Tx
               ll = Len(.Names(Tx).Comment)
               If Err.Number <> 0 Then
                   m = m + 1
                   .Names.Add(Tx, "_", False).Comment = "Mieter " & m
                   Debug.Print .Names.Count, .Names(Tx).Name, .Names(Tx).Comment
                   Err.Clear
               Else
                   Cells(i, 1) = .Names(Tx).Comment
               End If
           End If
       Next i
       For Each Nm In ThisWorkbook.Names
           ActiveSheet.Columns(1).Replace Nm.Name, Nm.Comment
       Next Nm
   End With
End Sub

  5. Speichere deine Datei als .xlsm, um die Makros zu aktivieren.

  6. Schließe den VBA-Editor und gehe zurück zu Excel.

  7. Führe das Makro aus:

    • Drücke ALT + F8, wähle cmd_anonym_Click und klicke auf Ausführen.

Häufige Fehler und Lösungen

  • Fehler: "Laufzeitfehler 1004"

    • Lösung: Stelle sicher, dass die Mieter in Spalte A korrekt eingetragen sind und die zweite Spalte tatsächlich "Wohnungsmieter" enthält.

  • Fehler: Makro funktioniert nicht

    • Lösung: Überprüfe, ob die Datei im richtigen Format (.xlsm) gespeichert ist und ob die Makros aktiviert sind.

  • Fehler: Doppelte Mieter werden nicht korrekt ersetzt

    • Lösung: Achte darauf, dass der Code den Namen korrekt speichert und bei doppelten Einträgen richtig ersetzt.

Alternative Methoden

Wenn VBA nicht Dein bevorzugtes Werkzeug ist, kannst Du auch die Suchen und Ersetzen-Funktion in Excel verwenden, allerdings ist dies weniger flexibel.

  1. Wähle die Zellen in Spalte A aus.
  2. Drücke Strg + H, um das Dialogfeld Suchen und Ersetzen zu öffnen.
  3. Gib den Namen des Mieters in das Feld Suchen nach ein.
  4. Gib die gewünschte Bezeichnung in das Feld Ersetzen durch ein.
  5. Klicke auf Alle ersetzen.

Dies ist jedoch nicht für eine große Anzahl von Namen optimal, da es manuell durchgeführt werden muss.

Praktische Beispiele

Angenommen, Deine Liste sieht folgendermaßen aus:

A B
Max Mustermann Wohnungsmieter
F. Meier Wohnungsmieter
ABC GmbH Gewerbemieter
C. Schuster Wohnungsmieter
F. Meier Wohnungsmieter

Nach dem Ausführen des Codes wird die Liste so aussehen:

A B
Mieter 1 Wohnungsmieter
Mieter 2 Wohnungsmieter
ABC GmbH Gewerbemieter
Mieter 3 Wohnungsmieter
Mieter 2 Wohnungsmieter

Tipps für Profis

  • Fehlerbehandlung: Verwende On Error Resume Next, um unerwartete Fehler zu vermeiden, aber sei vorsichtig, da dies auch andere Fehler ignorieren kann.
  • Modularität: Teile deinen Code in kleinere, wiederverwendbare Funktionen auf, um die Lesbarkeit und Wartbarkeit zu verbessern.
  • Dokumentation: Kommentiere Deinen Code ausgiebig, damit Du und andere Nutzer ihn später besser verstehen.

FAQ: Häufige Fragen

1. Wie viele Mieter kann ich mit diesem Code anonymisieren?
Der Code ist darauf ausgelegt, bis zu 500 Einträge in Spalte A zu verarbeiten. Du kannst die Schleife anpassen, um mehr Zeilen zu berücksichtigen.

2. Funktioniert der Code in Excel 2016 und älter?
Ja, der VBA-Code sollte in Excel 2016 sowie in älteren Versionen problemlos funktionieren.

3. Was passiert mit den Gewerbemietern?
Die Namen der Gewerbemieter bleiben unverändert, da der Code nur auf "Wohnungsmieter" reagiert.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige