Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Wert finden und ersetzen


Betrifft: Wert finden und ersetzen von: Martin Pilk
Geschrieben am: 15.07.2018 09:58:07

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

  

Betrifft: AW: Wert finden und ersetzen von: AlterDresdner
Geschrieben am: 15.07.2018 18:59:15

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


  

Betrifft: AW: Wert finden und ersetzen von: Martin
Geschrieben am: 19.07.2018 18:15:46

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.


  

Betrifft: AW: Wert finden und ersetzen von: AlterDresdner
Geschrieben am: 19.07.2018 21:56:27

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



  

Betrifft: AW: Wert finden und ersetzen von: Martin
Geschrieben am: 20.07.2018 20:16:30

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



  

Betrifft: AW: Wert finden und ersetzen von: AlterDresdner
Geschrieben am: 20.07.2018 22:08:51

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


  

Betrifft: AW: Wert finden und ersetzen von: Martin
Geschrieben am: 21.07.2018 01:06:28

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

Gruß Martin


Beiträge aus dem Excel-Forum zum Thema "Wert finden und ersetzen"