Microsoft Excel

Herbers Excel/VBA-Archiv

Wert finden und Verketten | Herbers Excel-Forum


Betrifft: Wert finden und Verketten von: Michael
Geschrieben am: 04.03.2010 13:28:32

Hallo zusammen,

ich hoffe ihr könnt mir helfen.

Um das Problem besser zu verstehen hier ein Bespiel:

https://www.herber.de/bbs/user/68380.xls

Generell möchte ich ein Wert in Spalte B finden die Spalte A ausgeben und wenn es mehrmals vorkommen sollte dann verketten.

Danke

  

Betrifft: AW: Wert finden und Verketten von: fcs
Geschrieben am: 04.03.2010 16:02:59

Hallo Michael,

die Formellösung erfordert nach meiner Einschätzung eine Hilfsberechnung.
Etwas übersichtlicher ist ggf. eine ggf. eine benutzerdefinierte Funktion erstellt unter VBA.

In der Datei sind beide Lösungsvorschläge enthalten.
https://www.herber.de/bbs/user/68384.xls

Gruß
Franz


  

Betrifft: AW: Wert finden und Verketten von: Michael
Geschrieben am: 05.03.2010 13:24:30

Super!! Vielen Dank.

Jetzt noch eine Frage.

Kann ich die Funktion (Makro) so anpassen dass ich nur die ersten zwei Werte ausgebe also "Wert1_Wert2" und nicht "Wert1_Wert2_Wert3".


  

Betrifft: AW: Wert finden und Verketten von: fcs
Geschrieben am: 05.03.2010 13:51:08

Hallo Michael,

kein Problem. Man muss dann die max. Anzahl Fundstellen vorgeben und die For-Next-Scleife verlassen, wenn diese Zahl erreicht ist.

Schaut dann wie folgt aus, wenn man die max. Anzahl, als Parameter der Funktion vorgibt.

Gruß
Franz

Function fncTexteVerketten(Suchwert, Bereich As Range, Suchspalte As Long, _
  Textspalte As Long, _
  Optional MaxAnzahl As Long, _
  Optional Trenntext As String = "; ") As String
  'Suchwert= zu suchender Wert
  'Bereich mit zu suchenden und auszugebenden Werten
  'Suchspalte = Nr. der Spalte innerhalb von Bereich in der der Suchwert gesucht _
      werden soll
  'Textspalte = Nr. der Spalte innerhalb von Bereich in der im Ergebnis zurückzugebenden _
      Werte stehen
  'MaxAnzahl = optionaler Wert für die Anzahl der zurückzugebenden Werte
  'Trenntext = optionaler Wert für den zwischen den gefundenen Werten einzufügenden Trenntext _
                Vorgabe = "; "
  Dim zeile As Long, iZaehler As Long
  For zeile = 1 To Bereich.Rows.Count
    If Bereich(zeile, Suchspalte) = Suchwert Then
      If fncTexteVerketten = "" Then
        fncTexteVerketten = Bereich(zeile, Textspalte).Text
      Else
        fncTexteVerketten = fncTexteVerketten & Trenntext & Bereich(zeile, Textspalte).Text
      End If
      If MaxAnzahl > 0 Then
        iZaehler = iZaehler + 1
        If iZaehler = MaxAnzahl Then Exit For
      End If
    End If
  Next
End Function


Formelbeispiel:
=fncTexteverketten(F10;$A$1:$B$7;2;1;2;"; ")



  

Betrifft: AW: Wert finden und Verketten von: Michael
Geschrieben am: 05.03.2010 14:34:45

Danke.

Aber das meinte ich gar nicht ;-). Ich meine den Wert im Namen. Also ich hab ja STUHL_TISCH_FENSTER und AUTO_HEIZUNG_TELEFON als Suchspalte. Jetzt möchte ich nur STUHL_TISCH; AUTO_HEIZUNG aufgeben.

Also nach dem zweiten "_" benötige ich die Werte nicht mehr.


  

Betrifft: AW: Wert finden und Verketten von: fcs
Geschrieben am: 05.03.2010 15:26:18

Hallo Michael,

kleines Mißverständnis.

Auch diese Abtrennung kann man natürlich umsetzen.

Gruß
Franz

Function fncTexteVerketten(Suchwert, Bereich As Range, Suchspalte As Long, _
  Textspalte As Long, _
  Optional MaxAnzahl As Long, Optional MaxTrenn As String = "_", _
  Optional Trenntext As String = "; ") As String
  'Suchwert= zu suchender Wert
  'Bereich mit zu suchenden und auszugebenden Werten
  'Suchspalte = Nr. der Spalte innerhalb von Bereich in der der Suchwert gesucht _
      werden soll
  'Textspalte = Nr. der Spalte innerhalb von Bereich in der im Ergebnis zurückzugebenden _
      Werte stehen
  'MaxAnzahl = optionaler Wert für die Anzahl der zurückzugebenden Werte
  'MaxTrenn  = optionaler Wert für Zeichen zwischen den Wörtern des gefundenen Textes _
                Vorgabe = "_"
  'Trenntext = optionaler Wert für den zwischen den gefundenen Werten einzufügenden Trenntext _
                Vorgabe = "; "

  'Formelbeispiel: =fncTexteverketten(F10;$A$1:$B$7;2;1;2;"_";"; ")
  '                =fncTexteverketten(F10;$A$1:$B$7;2;1;2)
  
  Dim zeile As Long, iZaehler As Long, sText As String
  For zeile = 1 To Bereich.Rows.Count
    If Bereich(zeile, Suchspalte) = Suchwert Then
      sText = Bereich(zeile, Textspalte).Text
      If MaxAnzahl > 0 Then
        'Anzahl der Trennzeichen im gefundenen Text
        iZaehler = Len(sText) - Len(Replace(sText, MaxTrenn, ""))
        If iZaehler >= MaxAnzahl Then
          'Text ab dem MaxAnzahl-ten Trennzeichen abtrennen  - Substitute = WECHSELN
          sText = Left(sText, InStr(1, Application.WorksheetFunction.Substitute(sText, _
                MaxTrenn, "$_$", MaxAnzahl), "$_$") - 1)
        End If
      End If
      If fncTexteVerketten = "" Then
        fncTexteVerketten = sText
      Else
        fncTexteVerketten = fncTexteVerketten & Trenntext & sText
      End If
    End If
  Next
End Function



  

Betrifft: AW: Wert finden und Verketten von: Michael
Geschrieben am: 05.03.2010 15:38:38

PERFEKT!!! Ich danke Dir!!!


Beiträge aus den Excel-Beispielen zum Thema "Wert finden und Verketten"