Wert finden und ersetzen

Forumthread: Wert finden und ersetzen

15.07.2018 09:58:07

Martin

Hallo,
ich versuche Texte aus einer bestimmten Spalte/Zeile ich die richtige zu bekommen.
Ich hab mal versucht es zu erklären und ein bild dazu findet ihr hier https://ibb.co/kus4j8.
IF in Spalte 2 (B) ZBS steht (Zeile 5) suche die Zeile in der Spalte 16 (J) mit der Nummer aus Spalte 1 (A) beginnt (Zeile 2) und Trage statt ZBS den wert aus Spalte 20 ein (Zeile 5).

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Wert finden und ersetzen

15.07.2018 18:59:15

AlterDresdner
AW: Wert finden und ersetzen

19.07.2018 18:15:46

Martin
AW: Wert finden und ersetzen

19.07.2018 21:56:27

AlterDresdner
AW: Wert finden und ersetzen

20.07.2018 20:16:30

Martin
AW: Wert finden und ersetzen

20.07.2018 22:08:51

AlterDresdner
AW: Wert finden und ersetzen

21.07.2018 01:06:28

Martin

AW: Wert finden und ersetzen

15.07.2018 18:59:15

AlterDresdner

Hallo Martin,
so sollte es gehen:

Sub ZBS_Finden()
Dim erg As Range, zeile As Long
Dim zeilenr As Long, Nummer As String
With ActiveSheet
Set erg = .Range("B:B").Find(what:="ZBS", Lookat:=xlWhole)
If Not erg Is Nothing Then
Do
zeile = erg.Row
Nummer = .Cells(zeile, 1)
For zeilenr = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
If InStr(1, .Cells(zeilenr, 10), Nummer) = 1 Then
.Cells(zeile, 2) = .Cells(zeilenr, 14)
Exit For
End If
Next zeilenr
Set erg = .Range("B:B").FindNext(erg)
Loop Until erg Is Nothing
End If
End With
End Sub

Gruß der (ebenfalls) Martin

AW: Wert finden und ersetzen

19.07.2018 18:15:46

Martin

Hi, schon mal vielen dank für den Code. In meinem Beispiel hat er wunderbar geklappt auch wenn ich nicht weiß wieso. Leider nicht in der richtigen Tabelle.
1.

 If InStr(1, .Cells(zeilenr, 10), Nummer) = 1 Then

Wenn ich das richtig verstehe wird hier in Spalte 10 der Wert aus der Variable Nummer gesucht (Spalte 2 - Ende) aber die nummer steht doch in Spalte 16 oder hab ich da was falsch verstanden.
2.

.Cells(zeile, 2) = .Cells(zeilenr, 14)

Hier wird die Celle kopiert. Wieso Spalte 14 und nicht 20?. Ich hab vergessen zusagen das ich _ den wert in Spalte 20 über einen Sverweis erhalte. Ich hab das mal so gelöst weiß aber nicht ob das richtig ist da ich noch ein Problem habe.


.Cells(zeile, 2) = .Cells(zeilenr, 20).value

3.
Das andere Problem ist, das nicht sichergestellt ist das der Wert in der Tabelle ein zweites _ mal existiert. Und das macht aus


Loop Until erg Is Nothing

eine endlos Schleife wenn ich das richtg verstehe.

AW: Wert finden und ersetzen

19.07.2018 21:56:27

AlterDresdner

Hallo Martin,
noch mal der Code mit etwas Kommentar.
Es ist unerheblich, welche Zahlen in Zeile 1 stehen, nur die Spalten sind entscheidend.
Falls die Zahlen in zeile 1 Deine richtigen Spalten darstellen sollten,
müsstest Du den Code entsprechend anpassen. Dass mit dem .Value ist korrekt.
Gruß der Martin

Sub ZBS_Finden()
Dim erg As Range, zeile As Long
Dim zeilenr As Long, Nummer As String
With ActiveSheet
Set erg = .Range("B:B").Find(what:="ZBS", Lookat:=xlWhole, Searchdirection:=xlNext)
'erstes Suchen nach ZBS in Spalte B, beginnend in Zeile 1, abwärts
If Not erg Is Nothing Then 'wenn überhaupt vorhanden
Do
zeile = erg.Row
Nummer = .Cells(zeile, 1)
For zeilenr = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
'Suche nach der Nummer in Spalte J (also 10)
If InStr(1, .Cells(zeilenr, 10), Nummer) = 1 Then
'Nummer gefunden
.Cells(zeile, 2) = .Cells(zeilenr, 14)
'aus Spalte N (also 14) Wert übernehmen
Exit For 'For zeilenr verlassen
End If
Next zeilenr
Set erg = .Range("B:B").FindNext(erg)
'Suche nach dem nächsten Auftreten von ZBS
'wenn nicht mehr da =>erg=Nothing
Loop Until erg Is Nothing
End If
End With
End Sub

AW: Wert finden und ersetzen

20.07.2018 20:16:30

Martin

Hi,
ich hab es jetzt soweit hinbekommen. Ich hatte in Spalte 16 ein führendes Leerzeichen das ich nicht wusste somit wurde das Ergebnis immer 2 und nicht 1 ;-) das habe ich jetzt mit LTrim behoben.
es werden jetzt die werte übertragen wenn die die Nummer jedoch nicht gefunden wird (Punkt 3 meiner vorherigen Nachricht) ist erg niemals Nothing und ich habe eine endlos Schleife. Ich habe ca 1000 Zeilen, kann man das nicht begrenzen wenn die Nummer in den 1000 Zeilen nicht gefunden wird? Meinetwegen indem man den wert in Spalte 2 von ZBS in ZBS not found ändert.
Hier ist der Code:

Sub Test()
Dim erg As Range, zeile As Long
Dim zeilenr As Long, Nummer As String
With ActiveSheet
Set erg = .Range("B:B").Find(what:="ZBS", Lookat:=xlWhole)
If Not erg Is Nothing Then
Do
zeile = erg.Row
Nummer = .Cells(zeile, 1)
For zeilenr = 7 To .Cells(Rows.Count, 1).End(xlUp).Row
If InStr(1, LTrim(.Cells(zeilenr, 16)), Nummer) = 1 Then
.Cells(zeile, 2).Font.Color = RGB(255, 0, 0)
.Cells(zeile, 2) = .Cells(zeilenr, 20)
'MsgBox "Zeile: " & zeile & "     Nummer: " & Nummer & " " & vbLf & "Ergebnis:" &  _
vbLf & "Zeile: " & zeilenr & " " & vbLf & "Nummer: " & InStr(1, LTrim(.Cells(zeilenr, 16)), Nummer) & " " & vbLf & "Erg = " & erg
Exit For
End If
Next zeilenr
Set erg = .Range("B:B").FindNext(erg)
'MsgBox erg
Loop Until erg Is Nothing
End If
End With
End Sub

AW: Wert finden und ersetzen

20.07.2018 22:08:51

AlterDresdner

Hallo Martin,
das hatte ich nicht bedacht, also:

Sub ZBS_Finden()
Dim erg As Range, zeile As Long
Dim zeilenr As Long, Nummer As String
With ActiveSheet
Set erg = .Range("B:B").Find(what:="ZBS", Lookat:=xlWhole, Searchdirection:=xlNext)
'erstes Suchen nach ZBS in Spalte B, beginnend in Zeile 1, abwärts
If Not erg Is Nothing Then 'wenn überhaupt vorhanden
Do
zeile = erg.Row
Nummer = .Cells(zeile, 1)
For zeilenr = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
'Suche nach der Nummer in Spalte J (also 10)
If InStr(1, .Cells(zeilenr, 10), Nummer) = 1 Then
'Nummer gefunden
.Cells(zeile, 2) = .Cells(zeilenr, 14)
'aus Spalte N (also 14) Wert übernehmen
GoTo Naechste 'For zeilenr verlassen
End If
Next zeilenr
MsgBox "Nummer " & Nummer & " nicht gefunden, ZBS geändert", _
vbInformation, "Nummer nicht gefunden"
.Cells(zeile, 2) = "ZBS not found"
Naechste: Set erg = .Range("B:B").FindNext(erg)
'Suche nach dem nächsten Auftreten von ZBS
'wenn nicht mer da =>erg=Nothing
Loop Until erg Is Nothing
End If
End With
End Sub

Gruß der Martin

AW: Wert finden und ersetzen

21.07.2018 01:06:28

Martin

Hallo Martin und vielen vielen dank. jetzt funktioniert alles wunderbar.
Gruß Martin

Beliebteste Forumthreads (12 Monate)

Forumthread: Wert finden und ersetzen

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

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread