Eine bestimmte Zahl ersetzen

Bild

Betrifft: Eine bestimmte Zahl ersetzen
von: erichm
Geschrieben am: 07.09.2015 06:36:35

Hallo,
in einem definierten Tabellenbereich (z.B. B3 bis I122 ) soll nach einer Zahl gesucht werden und diese nach einem Muster ersetzt werden:
1. Suche die Zahl 1027 in den Spalten B bis I
2. Ersetze die Zahl 1027:
aufsteigend um 1 beginnend mit 1029
also zuerst mit 1029
dann mit 1030
dann mit 1031
usw…. bis 1038
dann wieder mit 1029
dann mit 1030 usw….
Anbei eine Tabelle mit der Aufgabenstellung; dort kommt die 1027 genau 20x vor:
https://www.herber.de/bbs/user/100056.xlsm
Besten Dank!
mfg

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: JoWE
Geschrieben am: 07.09.2015 07:27:53
Hallo
vielleicht so: Makro in Tabelle

Private Sub ersetzer()
    Dim zähler As Integer
    Dim ze As Range
    zähler = 1029
    For Each ze In Range("B2:I122")
        If ze = 1027 Then
            ze.Value = zähler
            zähler = zähler + 1
        End If
    Next
End Sub
Gruß
Jochen

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: erichm
Geschrieben am: 07.09.2015 08:06:24
Danke - noch nicht ganz:
Der Zähler müsste nach +10 wieder neu bei + 1 beginnen.
mfg

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: JoWE
Geschrieben am: 07.09.2015 08:15:57
ja - vergessen, sorry.
Aber: Das hättest Du eigentlich auch selbst überlegen können, oder? :-)
So z.B.: Hinter
"zähler = zähler + 1" fügst Du dies hier ein> "If zähler > 1038 Then zähler = 1029"
Gruß
Jochen

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: erichm
Geschrieben am: 09.09.2015 06:44:04
SORRY - da war ich am Montagmorgen noch nicht ganz fit; stimmt die Ergänzung muss ich drauf haben.
(konnte mich leider erst heute wieder melden)
Es ist jetzt aber eine neue Frage aufgetaucht - nachdem ich die Zahlen ersetzt habe. Siehe neue Datei mit der konkreten Aufgabenbeschriebung:
1. Durchsuche jede Zeile von B bis I (Zeilen 4 bis 123)
2. Prüfe ob eine Zahl pro Zeile doppelt vorkommt
3. Wenn keine Doppelte Zahl: nichts tun
4. Wenn eine Zahl doppelt, dann ersetze die erste Doppelte mit einer Zahl aus der Range D1:AI1 die aber bisher nicht in dieser Reihe ist.

Die Doppelten finde ich per Formel, aber beim ersetzen hakt es aus.
https://www.herber.de/bbs/user/100086.xlsm
Besten Dank nochmal!
mfg

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: JoWE
Geschrieben am: 09.09.2015 13:23:11
Hallo Erich,
das scheint mir aufwändig! Überdies ist der Bereich von D1 bis AI1 ja auch irgendwann abgearbeitet. Ginge es dann wieder in Spalte D von vorn los, gäbe es ja erneut Doppler.
Dein Problem ließe sich vermutlich mittels eines Arrays oder einer Collection lösen.
Das aber sind nicht wirklich meine Domänen. Stelle die Frage daher für die hier tätigen Spezialisten offen.
Gruß
Jochen

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: erichm
Geschrieben am: 09.09.2015 19:22:31
Hallo Jochen,
danke für die Rückmeldung.
Aber um Missverständnissen vorzubeugen:
Jede Zeile die geprüft wird hat nur 8 Zahlen.
Wenn eine Zahl doppelt vorkommt und diese - nur eine der doppelten - mit einer aus D1:AI1 getauscht wird gibt es keine weitere Doppelung (natürlich unter der Voraussetzung, dass aus D1:AI1 eine Zahl gewählt wird, die nicht in dieser Zeile vorkommt; es kann auch eine x-beliebige Zahl aus D1:AI1 gewählt werden, da ist kein systematisches abarbeiten erforderlich).
Es wird ja jede Zeile für sich nur auf Doppelte geprüft und nicht mehrer Zeilen untereinander.
mfg

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: JoWE
Geschrieben am: 09.09.2015 21:15:55
Hi Erich,
hier eine (zugegebenermaßen nicht elegante) Lösung genau für Deine Musterarbeitsmappe, die nur mit Deinen Hilfsspalten (Doppler > 1) klappt:
pre>


Sub ersetzen2()
    Dim r As Long
    Dim e1 As Long
    Dim e2 As Long
    Dim sp As Long
    For r = 4 To 123
        e1 = 0
        e2 = 3
        For sp = 2 To 9
            If Cells(r, sp + 9) > 1 Then
                e1 = e1 + 1
                If e1 > 1 Then
                    e2 = e2 + 1
                    Cells(r, sp) = Cells(1, e2)
                End If
            End If
        Next
    Next
End Sub
Wie schon gesagt, Arrays, Dictionarys oder Collections sind nicht meine Domänen.
Gruß
Jochen

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: erichm
Geschrieben am: 10.09.2015 06:27:46
Hallo Jochen,
danke, das wär schon mal ein Ansatz.
In der Zeile 46 klappt die Lösung aber nicht. Wenn ich den Code ein 2. mal laufen lasse, ändert er aber nichts mehr obwohl die Bedingung größer 1 erfüllt ist.
Mehrere Anpassungsversuche sind gescheitert.
Vielleicht gibts noch eine Ergänzung oder Alternative.....
....kurz vor dem Ziel!
mfg

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: JoWE
Geschrieben am: 10.09.2015 07:16:22
Erich,
schau mal ob's so geht:

Sub ersetzen2()
    Dim r As Long
    Dim e1 As Long
    Dim e2 As Long
    Dim sp As Long
    Dim sp2 As Integer
    For r = 4 To 123
        e1 = 0
        e2 = 3
        For sp2 = 1 To 4
            For sp = 2 To 9
                e2 = e2 + 1
                If Cells(r, sp + 9) > 1 Then
                    e1 = e1 + 1
                    If e1 > 1 Then
                        'e2 = e2 + 1
                        If Cells(r, sp) = Cells(1, e2) Then e2 = e2 + 1
                        Cells(r, sp) = Cells(1, e2)
                    End If
                End If
            Next
        Next
    Next
End Sub

Gruß
Jochen

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: erichm
Geschrieben am: 10.09.2015 20:59:51
Hallo Jochen,
danke; wenn ich den Code 2mal laufen lasse habe ich keine Doppelten mehr!!
Frage: wie kommen denn die Zahlen von D1:AI1 in die jeweilige Reihe, da werde ich aus dem Code nicht schlau.
mfg

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: JoWE
Geschrieben am: 11.09.2015 06:59:42
Erich,
hier:
Userbild
Gruß
Jochen

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: erichm
Geschrieben am: 11.09.2015 07:41:12
OK; habe jetzt mal folgendes gemacht um das zu verstehen:
1. Die Zahlen von D1:AI1 habe ich von 1 - 32 durchnumeriert.
2. Dann erkenne ich besser welche Zahlen getauscht werden.
3. Es werden jetzt immer beide Zahlen die doppelt sind getauscht - das ist aber auch ok (1 Tausch hätte mir gereicht).
4. Jetzt zählt der Code quasi die Zellen ab B4 bis I123; z.B. C4 ist die zweite Zahl; wenn die doppelt wäre würde er diese mit 2 (aus E1) ersetzen.
5. Das bedeutet ich benötige die Hilfsspalten gar nicht mehr??
Abschließend habe ich folgendes getestet:
1. Den Spaltenbereich mit den Doppelten um eine Spalte erweitert (=Spalten B bis J); bisher Spalten B bis I
2. Den Code von jeweils To 9 bzw. + 9 auf To 10 bzw. + 10 angepasst
3. Das hat aber jetzt nicht funktioniert.
4. Da habe ich einen Teilbereich noch nicht verstanden.
Besten Dank für eine Info. Evtl. liegt es an der Zeile For sp2 = 1 To 4; was bedeutet diese?
mfg

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: JoWE
Geschrieben am: 11.09.2015 10:23:47
Erich,
die Erweiterung ist kein Problem; klappt bei mir demzufolge ohne Probleme
Die Hilfsspalten bleiben erforderlich, müssen also auch um die eine Spalte erweitert und die Formeln angepasst werden. Der Teil "sp = 2 to 4" sorgt für einen 4-maligen Durchlauf der Prozedur.
Zum Verstehen von Code hilft nur: Lesen, Lesen, Lesen.
Zum Beispiel hier:
https://www.matse.itc.rwth-aachen.de/dienste/public/show_document.php?id=7946
Gruß
Jochen
P.S. Ab jetzt bitte einen neuen Thread

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: JoWE
Geschrieben am: 11.09.2015 12:18:20
Erich,
vielleicht habe ich doch was ohne die Hilfsspalten:

Option Explicit
'*********************************************************************************************
'Aufgabe: Doppler in Zeilen ersetzen durch Ersetzwerte aus Zeile 1
'in den Zeilen sollen im Ergebnis keine Doppler stehen bleiben
'*********************************************************************************************
Sub start()
   Dim wb As Workbook
   Dim sh As Worksheet
   Set wb = ThisWorkbook
   Set sh = wb.Sheets("Tabelle1") 'Anpassen
   Dim dlZe As Long      'Laufariable geht alle Zeile durch
   Dim dlSp As Long      'Laufvaribale geht jede Splate jeder Zeile durch
   Dim pruefer As Long   'wenn pruefer >1 ist ist der Wert in der Zeile doppelt
   Dim n As Long         'wenn n = 2 wird der gefundene Doppler ersetzt
   Dim ersetzSp As Long  'Variable für die Spalte die den Ersetzwert aus Zeile 1 liefert
   Dim ber As String     'der Bereich für die Dopplersuch wird in jede Zeile neu gesetzt
  
   'um ein wenig Schreibarbeit zu sparen
   With sh
    'los geht es in Zeile 4
    For dlZe = 4 To 123   'alle Zeilen werden "abgegrast"
      ersetzSp = 3: n = 0 'Startwert bestimmter Variablen für Durchlauf setzen
      'jetzt den Bereich für die gerade aktuelle Zeile setzen
      ber = .Range(.Cells(dlZe, 2), .Cells(dlZe, 10)).Address
      'jetzt wird die Zeile horizontal durchsucht
      For dlSp = 2 To 10
        'Inhalt der Zelle prüfen
        pruefer = Application.WorksheetFunction.CountIf(.Range(ber), .Cells(dlZe, dlSp))
        'gibt es den Wert in der Zeile mehrfach wird zunächst der Zähler n um den Wert 1 erhöht
        If pruefer > 1 Then n = n + 1
        'wenn n  > 1 ist wird der 2. Doppler ersetzt
        If n > 1 Then
          'die Splate die den Ersetzwert aus Zeile 1 liefert auf den Startwert 4 setzen
          ersetzSp = ersetzSp + 1
          'jetzt wird der Doppler ersetzt
          .Cells(dlZe, dlSp).Value = .Cells(1, ersetzSp).Value
          'der Ersetwert kann in der Zeile ja auch schon vorhanden sein
          'daher erneut aif Doppler prüfen
          If Application.WorksheetFunction.CountIf(.Range(ber), .Cells(dlZe, dlSp)) > 1 Then
            'ein erneuter Doppler wird durch einen anderen Ersetzwert ersetzt
            ersetzSp = ersetzSp + 1
            .Cells(dlZe, dlSp).Value = .Cells(1, ersetzSp).Value
          End If
        End If
      Next
    Next
  End With
End Sub
Gruß
Jochen

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: erichm
Geschrieben am: 11.09.2015 19:15:11
DANKE - das wäre natürlich super!
(den letzten Code habe ich mittlerweile weitgehend kapiert; besten Dank für den Lesetipp, das ist gut aufbereitet)
Der neue Code klappt aber noch nicht so richtig. Ich habe mal die letzte Datei mit den 8 Spalten hochgeladen und Deinen neuen Code eingefügt (die 10 dann auf die 9 geändert im Code). Ferner einige Tabellen "zum üben" kopiert.
Habe zwar schon probiert, aber da bin ich vom Verständnis doch noch zu weit weg.
Vielleicht findet man noch die richtige Lösung ohne Hilfsspalten:
https://www.herber.de/bbs/user/100143.xlsm
DANKE!
mfg

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: JoWE
Geschrieben am: 11.09.2015 20:20:53
und nochmal:

Option Explicit
'*********************************************************************************************
'Aufgabe: Doppler in Zeilen ersetzen durch Ersetzwerte aus Zeile 1
'in den Zeilen sollen im Ergebnis keine Doppler stehen bleiben
'*********************************************************************************************
Sub start()
   Dim wb As Workbook
   Dim sh As Worksheet
   Set wb = ThisWorkbook
   Set sh = wb.Sheets("Tabelle1") 'Anpassen
   Dim dlZe As Long      'Laufariable geht alle Zeile durch
   Dim dlSp As Long      'Laufvaribale geht jede Splate jeder Zeile durch
   Dim pruefer As Long   'wenn pruefer >1 ist ist der Wert in der Zeile doppelt
   Dim n As Long         'wenn n = 2 wird der gefundene Doppler ersetzt
   Dim ersetzSp As Long  'Variable für die Spalte die den Ersetzwert aus Zeile 1 liefert
   Dim ber As String     'der Bereich für die Dopplersuch wird in jede Zeile neu gesetzt
  
   'um ein wenig Schreibarbeit zu sparen
   With sh
    'los geht es in Zeile 4
    For dlZe = 4 To 123   'alle Zeilen werden "abgegrast"
      ersetzSp = 3: n = 0 'Startwert bestimmter Variablen für Durchlauf setzen
      'jetzt den Bereich für die gerade aktuelle Zeile setzen
      ber = .Range(.Cells(dlZe, 2), .Cells(dlZe, 9)).Address
      'jetzt wird die Zeile horizontal durchsucht
      For dlSp = 2 To 9
        sh.Cells(dlZe, dlSp).Select
        'Inhalt der Zelle prüfen
        pruefer = Application.WorksheetFunction.CountIf(.Range(ber), .Cells(dlZe, dlSp))
        'gibt es den Wert in der Zeile mehrfach wird zunächst der Zähler n um den Wert 1 erhöht
        If pruefer > 1 Then n = n + 1
        'wenn n  > 1 ist wird der 2. Doppler ersetzt
        If n > 1 Then
          'die Splate die den Ersetzwert aus Zeile 1 liefert auf den Startwert 4 setzen
          ersetzSp = ersetzSp + 1
          'jetzt wird der Doppler ersetzt
          .Cells(dlZe, dlSp).Value = .Cells(1, ersetzSp).Value
          'der Ersetwert kann in der Zeile ja auch schon vorhanden sein
          'daher erneut aif Doppler prüfen
          If Application.WorksheetFunction.CountIf(.Range(ber), .Cells(dlZe, dlSp)) > 1 Then
            'ein erneuter Doppler wird durch einen anderen Ersetzwert ersetzt
            ersetzSp = ersetzSp + 1
            .Cells(dlZe, dlSp).Value = .Cells(1, ersetzSp).Value
            If Application.WorksheetFunction.CountIf(.Range(ber), .Cells(dlZe, dlSp)) > 1 Then
                'ein erneuter Doppler wird durch einen anderen Ersetzwert ersetzt
                ersetzSp = ersetzSp + 1
                .Cells(dlZe, dlSp).Value = .Cells(1, ersetzSp).Value
                If Application.WorksheetFunction.CountIf(.Range(ber), .Cells(dlZe, dlSp)) > 1  _
Then
                    'ein erneuter Doppler wird durch einen anderen Ersetzwert ersetzt
                    ersetzSp = ersetzSp + 1
                    .Cells(dlZe, dlSp).Value = .Cells(1, ersetzSp).Value
                End If
            End If
          End If
        End If
      Next
    Next
  End With
End Sub
mit immer mehr Wiederholung wird's irgendwann.
Das Problem sind die Ersetzwerte, die ja auch alle immer mal in der Tabelle enthalten sind und jeweils neue Doppler bilden.
Gruß
Jochen

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: erichm
Geschrieben am: 11.09.2015 22:14:01
KLASSE - die ersten Tests haben alle geklappt; werde das morgen intensivieren und melde mich nochmal!
Auf alle Fälle schon mal allerbesten Dank für Deine Mühe und Deine Geduld.
mfg

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: erichm
Geschrieben am: 12.09.2015 07:17:16
@ Jochen: wie gewünscht ein neuer Thread zur Fortsetzung
https://www.herber.de/forum/messages/1447160.html

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: Gerd L
Geschrieben am: 07.09.2015 08:17:32
Hallo Erich!

Private Sub Ersetzen()
     Dim zahl As Integer
     Dim ze As Range
     zahl = 1029
     For Each ze In Range("B2:I122")
         If ze = 1027 Then
             ze.Value = zahl
             zahl = zahl + 1
             If zahl = 1039 Then zahl = 1029
         End If
     Next
 End Sub

Gruß Gerd

Bild

Betrifft: AW: Eine bestimmte Zahl ersetzen
von: erichm
Geschrieben am: 09.09.2015 06:37:57
DANKE - konnte mich leider erst heute melden!
(es ist aber eine neue Frage entstanden, deswegen wird der Beitrag nochmals auf offen gestellt)
mfg

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Eine bestimmte Zahl ersetzen"