Excel 2010- Suche in Spalte "Q" die Werte aus Spalte "R" und ersetze diese durch Spalte "S"

Question

Guten Abend,

ich habe in Spalte "Q" über 15.000 Einträge von Zahlen die ich teilweise ändern möchte. Ich Habe mit "Suchen und Ersetzen"  angefangen diese zu ändern was auch funktioniert. Nun meine Frage:ist es möglich das ich in Spalte "R" Zahlen eingebe (im Moment ca.300 Stück die in Spalte "Q" gesucht werden, und durch die in der Spalte"S" stehenden Zahlen ersetzt werden sollten.Diese Daten stammen aus Spalte "Q" stammen aus einem exportierten Outlook Kalender, deswegen zT. die Trennung mit Leerzeichen ; oder ,.

Als Beispiel wäre in R1 - suche "x1111" und ersetze mit "S1" ""xx1010" - dies wurde in "Q3" von "x1111 auf "xx1010" geändert.

x1717	x1111	xx1010
Dortmund	x1620	xx1020
xx1010	x1510	xx1030
Geburtstag Reiner	545454	xx1040
xx2260	x1680	xx1050
Fasnacht	202020	xx1060
x1620; 424242, 545454	797979	xx1070
x1150-x1510-xx1530-x1620-ect.	x5110	xx1080

Hoffe das ich mich Verständlich ausgedrückt habe und freue mich auf  Antworten.

Gruß

Rudolf

Answer 1

Hallo Rudolf,

verstehe ich das so richtig:

In Spalte R stehen die Zahlen, die in Spalte Q ersetzt werden sollen. In Spalte S stehen die "neuen Zahlen".

Also: gesucht wird Wert aus Zelle R1 und soll ersetzt werden durch Wert aus Zelle S1, dann Wert aus Zelle R2 suchen und mit Wert aus Zelle S2 ersetzen usw.

Gruß

M.O.

Answer 2

Hallo M.O.,

danke für Deine Rückmeldung.

Ich denke du hast es verstanden , trotzdem erkläre ich dir unten nochmal ausführlicher was ich meine :

In Spalte R stehen die Zahlen, die in Spalte Q gesucht und durch den Wer in Spalte S ersetzt werden sollen. In Spalte S stehen die "neuen Zahlen".

     Q           R          S

x1717	x1111	xx1010
Dortmund	x1620	xx1020
(x1111) xx1010	x1510	xx1030

Erklärung zum Beispiel oben:

Ich gebe in der Spalte R den Wert  x1111 ein , dieser Wert wird in Q gesucht und durch den Wert in S xx1010 ersetzt , dieser Wert wir in Q eingetragen

Also: gesucht wird Wert aus Zelle R1 in der Spalte Q und soll ersetzt werden durch Wert aus Zelle S1 und eingetragen werden in Spalte Q, dann Wert aus Zelle R2 suchen und mit Wert aus Zelle S2 ersetzen usw.

Gruß

Rudolf

Answer 3

Hallo Rudolf,

versuch es mal mit dem folgenden Makro in einem allgemeinen Modul:

Sub ersetzen()
Dim arrErsatz As Variant
Dim lngLetzte As Long
Dim lngZeile As Long
Dim i As Long

'Ermitteln der letzten beschriebenen Zeile in Spalte R
lngLetzte = ActiveSheet.Cells(Rows.Count, 18).End(xlUp).Row

'Spalten R und S (Suchbegriffe und Ersatz) in Array einlesen
arrErsatz = Range("R1:S" & lngLetzte)

'nun letzte beschrieben Zeile in Spalte Q ermitteln
lngLetzte = ActiveSheet.Cells(Rows.Count, 17).End(xlUp).Row

'Spalte Q durchlaufen
For lngZeile = 1 To lngLetzte
  'Zellinhalt mit Array vergleichen
  For i = 1 To UBound(arrErsatz, 1)
    If Cells(lngZeile, 17) = arrErsatz(i, 1) Then
      Cells(lngZeile, 17) = arrErsatz(i, 2)
      'diese Schleife beenden
      Exit For
    End If
   Next i
 Next lngZeile

End Sub

Gruß

M.O.

Answer 4

Hallo M.O.,

herzlichen Dank für das Makro das wieder perfekt funktioniert.

Eine Frage habe ich dazu: in Spalte "R" stehen ein paar Werte die mit "z" oder "w" beginnen, die ändert das  Programm nicht, - hast Du in deinem Makro eine Vorgabe das nur "x" annimmt?  Das würde mich Interessieren.

Diese paar Werte habe ich händisch geändert.

Danke

Gruß

Rudolf

Answer 5

Hallo Rudolf,

es wird nur nach den Suchbegriffen gesucht, die in Spalte R stehen.

Steht also in Spalte R der Wert x1101, dann wird genau dieser Wert ersetzt, also nur x1101 und nicht r1101. Sollte das als Joker gemeint gewesen sein, also ersetze in allen Werten, den Teil, der auf 1101 endet, dann müsste das Makro angepasst werden.

Gruß

M.O.

Answer 6

Hallo M.O.,

das ist genauso richtig wie Du das erklärt hast.

R1 suchen mit S1 in Q ersetzen. usw.

Ich nehme zur Darstellung aus Spalte Q einen Eintrag:

141414-x5080-x5180-z2626- ect. 

das sind die Zahlen die dann geändert stehen sollten:

xx1920-xx2020-xx2080-xx2280- ect.

Diese stehen alle auch in Spalte "R" und "S"

>Vor meiner Anfrage  habe ich jede Zahl einzeln geändert  mit "suchen und ersetzen" und mit "Alle ersetzen".

Wie in meiner Anfrage beschrieben funktioniert das auch mit Leerzeichen oder Strichpunkt oder Komma oder gemischt oder mit Text darin.<

Ich kann Dir leider nicht alle Möglichkeiten auflisten aber hier noch ein Beispiel zu"Q":

Dortmund; x1660  geändert Dortmund; xx1240

Hoffe das es Dir weiterhilft und du dein Makro anpassen kannst?

Danke

Wünsche Dir einen schönen Abend

Gruß

Rudolf

Answer 7

Hallo Rudolf,

hier das angepasste Makro:

Sub ersetzen()
Dim arrErsatz As Variant
Dim lngLetzte As Long
Dim lngZeile As Long
Dim i As Long

'Ermitteln der letzten beschriebenen Zeile in Spalte R
lngLetzte = ActiveSheet.Cells(Rows.Count, 18).End(xlUp).Row

'Spalten R und S (Suchbegriffe und Ersatz) in Array einlesen
arrErsatz = Range("R1:S" & lngLetzte)

'nun letzte beschrieben Zeile in Spalte Q ermitteln
lngLetzte = ActiveSheet.Cells(Rows.Count, 17).End(xlUp).Row

'Spalte Q durchlaufen
For lngZeile = 1 To lngLetzte
  'alle Suchwerte durchlaufen
  For i = 1 To UBound(arrErsatz, 1)
    'prüfen, ob gesuchte Werte in Zelle vorhanden ist
    If InStr(1, Cells(lngZeile, 17).Value, arrErsatz(i, 1)) Then
      'falls ja, dann Text ersetzen
      Cells(lngZeile, 17) = Replace(Cells(lngZeile, 17), arrErsatz(i, 1), arrErsatz(i, 2))
      'diese Schleife beenden
      Exit For
    End If
   Next i
 Next lngZeile

End Sub

Hier wird jetzt der Suchbegriff aus Spalte R in den Zellen von Spalte Q gesucht und durch den neuen Begriff aus Spalte S ersetzt. Schau mal ob das jetzt so funktioniert, wie du willst.

Gruß

M.O.

Answer 8

Hallo M.O.,

dein Makro soweit ich den größten Teil der vielen Zeilen durchgesehen habe klappt perfekt.

Ich bedanke mich für deine Zeit und den Aufwand und auch zu der Hilfe zum "allgemeinen Modul".

Wünsche einen guten Abend

Gruß

Rudolf