Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1628to1632
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

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

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
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...

322 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige