Verkettungsfunktion über Kriterium

Forumthread: Verkettungsfunktion über Kriterium

17.03.2016 15:38:47

jens

Hallo,
ich habe hier eine Verkettungsfunktion.

Function Kette2(Vorgabe As Range, Ketten As Range)
Dim Zelle As Range
Dim T As String
Application.Volatile
For Each Zelle In Vorgabe
T = Zelle.Value
Select Case T
Case "leer"
Kette2 = Kette2 & " "
Case ""
Kette2 = Kette2 & ""
Case Else
If T Like "[A-Z]" Or T Like "[A-Z][A-Z]" Then
Kette2 = Kette2 & Ketten.Worksheet.Cells(Ketten.Row, T).Text
Else
Kette2 = Kette2 & T
End If
End Select
Next
End Function

Mit dieser kann ich Spalten verketten unter Vorgabe der Spalte in Zellen.
Funktioniert auch super.
Aber wie baue ich hier noch eine Vorgabe ein.
Sprich wenn Vorgabe1 dann soll diese Vorschrift1 genommen werden.
Wenn Vorgabe2 dann soll die Vorschrift 2 genommen werden.

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Verkettungsfunktion über Kriterium

17.03.2016 16:03:43

jens

Dazu noch eine BSP Datei mit der umgesetzten Funktion in der Zelle Q3.
https://www.herber.de/bbs/user/104424.xlsm
Aber wie schaffe ich die Abhängigkeit entsprechende der Vorgabe in Zelle E3?

Warum die ganze Mühe mit einer für eine ...

17.03.2016 18:24:34

Luc:-?

…Anwendung von Ausdrücken als Argument ungeeigneten UDF*, Jens,
wenn doch schon eine normale und wesentlich flexiblere Fml reicht?
Q3[:]:=INDIREKT(INDEX(Tabelle2!A1:B14;VERGLEICH(E3;Tabelle2!A1:A14;0)+1;1)&ZEILE())&INDIREKT(INDEX(Tabelle2!A1:B14;VERGLEICH(E3;Tabelle2!A1:A14;0)+1;2)&ZEILE())
Ansonsten kannst du ja auch mal nach VJoin im Archiv RECHERCHiEren! ;-)
* Die UDF-Argumente sind datentyp-beschränkt deklariert, ein häufig zu findender Fehler, speziell bei Access u.Ä. gewohnten Pgmmierern. Dadurch kann eine solche UDF nicht wie das Gros der Xl-StandardFktt, sondern eher nur wie ~WENNs & Co benutzt wdn!
Feedback nicht unerwünscht! Gruß, Luc :-?
Besser informiert mit …

AW: Verkettungsfunktion über Kriterium

17.03.2016 17:28:53

Michael

Hallo Jens,
ich habe zwei Änderungen an Deiner Datei vorgenommen:
a) Tabelle 2 umsortiert, damit die Spaltenbuchstaben mit SVerweis verfügbar gemacht werden und
b) eine Function Kette3 angelegt, die als Parameter die beiden Spaltenbuchstaben "frißt":

Function Kette3(SpLi As String, SpRe As String, Ketten As Range)
Dim Zelle As Range
Dim T(1 To 2) As String
Dim i As Long
'Application.Volatile
T(1) = SpLi: T(2) = SpRe
For i = 1 To 2
Select Case T(i)
Case "leer"
Kette3 = Kette3 & " "
Case ""
Kette3 = Kette3 & ""
Case Else
If T(i) Like "[A-Z]" Or T(i) Like "[A-Z][A-Z]" Then
Kette3 = Kette3 & Ketten.Worksheet.Cells(Ketten.Row, T(i)).Text
Else
Kette3 = Kette3 & T(i)
End If
End Select
Next
End Function

Aufgerufen dann in Zeile 5 etwa mit:

=Kette3(SVERWEIS(E5;Tabelle2!$D$1:$F$5;2);SVERWEIS(E5;Tabelle2!$D$1:$F$5;3);A5:P5)

Das funktioniert solange gut, wie die beiden Spaltenbuchstaben nebeneinander liegen (das tun sie ja in allen Beispielen). Hier eingesetzt in Zeile 5...
Eigentlich wäre es noch viel einfacher, nur EINE Angabe zu verwenden, das skizziere ich noch schnell:

Function Kette4(Spalten As String, Ketten As Range)
Dim Zelle As Range
Dim T As Variant
Dim i As Long
'Application.Volatile
T = Split(Spalten, ",")
For i = LBound(T) To UBound(T)
Select Case T(i)
Case "leer"
Kette4 = Kette4 & " "
Case ""
Kette4 = Kette4 & ""
Case Else
If T(i) Like "[A-Z]" Or T(i) Like "[A-Z][A-Z]" Then
Kette4 = Kette4 & Ketten.Worksheet.Cells(Ketten.Row, T(i)).Text
Else
Kette4 = Kette4 & T(i)
End If
End Select
Next
End Function

Aufruf mit =Kette4(SVERWEIS(E6;Tabelle2!$H$1:$I$5;2);A6:P6)
Im Beispiel (Zeile 6) steht "Vorgabe 3" und SVerweis gibt den String "K,F,D" zurück - so kannst Du beliebig viele Spalten verwenden. Ein "von-bis" könnte man auch programmieren, aber das scheint ja erst mal nicht gefragt zu sein.
Die Datei: https://www.herber.de/bbs/user/104427.xlsm
Ach, ob das app.volatile sein muß... ich hab's auskommentiert.
Schöne Grüße,
Michael

AW: Verkettungsfunktion über Kriterium

18.03.2016 07:27:41

Jens

@ Michael.
Super Funkioniert zum Großteil schon sehr gut.
Eine Anmerkung hätte ich noch.
Bei Kette 3 gefällt mir sehr gut, dass wenn ich z.b in Vorgabe 1 schreibe dass Spalte A verketten werden soll und z.b danach ein Punk "." dann wird dies auch gemacht.
So Wäre es optimal. Aber es müsste erweiterbar sein. Sodass eine beliebige Anzahl an Spalten verknüft werden kann.
Ebenso sollte die Vorgabe der Spalten und auch Sonderzeichen oder Wörter immer jeweils durch eine Zelle erfolgen und nicht wie in deiner Kette4 über die Angabe des Spaltenbuchstaben in einer Zelle.
Ist diese Anpassung noch möglich?
(Siehe hierzu den Link)
https://www.herber.de/bbs/user/104437.xlsm

AW: Verkettungsfunktion über Kriterium

18.03.2016 11:31:46

Michael

Hallo Jens,
die Variante 4 gefällt Dir zwar auf Anhieb nicht, aber ich habe sie etwas erweitert auf Version 5:

Function Kette5(Spalten As String, Ketten As Range, SplitChar As String)
Dim Zelle As Range
Dim T As Variant
Dim i As Long
'Application.Volatile
T = Split(Spalten, SplitChar)
For i = LBound(T) To UBound(T)
Select Case T(i)
Case "leer"
Kette5 = Kette5 & " "
Case ""
Kette5 = Kette5 & ""
Case Else
If T(i) Like "[A-Z]" Or T(i) Like "[A-Z][A-Z]" Then
Kette5 = Kette5 & Ketten.Worksheet.Cells(Ketten.Row, T(i)).Text
Else
Kette5 = Kette5 & T(i)
End If
End Select
Next
End Function

Aufgerufen wird sie z.B. mit =Kette5(SVERWEIS(E7;Tabelle2!$H$1:$I$5;2);A7:P7;"|")
Als letzter Parameter wird das Trennzeichen eingegeben, in dem Fall der senkrechte Strich (altGr+Taste mit größer/kleiner-Zeichen).
Bei Eingabe von A|, |B|C|D|. als Vorgabe kommt der Text heraus: Hallo, hier ist User.
So IST das beliebig erweiterbar, und Du kannst auch mehrere Zeichen zwischen den Trennzeichen verwenden, wie Komma+Leerzeichen zwischen A und B.
Mit "Excel gut" sollte es Dir ein Leichtes sein, die gewünschten "variablen Spalten" selbst zu einem String mit dieser Syntax zu verketten - das geht mit Excel-Mitteln, ohne das Makro zu ändern.
Schöne Grüße,
Michael

AW: Verkettungsfunktion über Kriterium

18.03.2016 11:47:29

jens

Hallo,
danke für die Hilfe.
Mein Problem ist blos, das die kette2 mit den Spalten arbeitet und ich nun zusätzlich noch eine andere Art einbauen muss. Bzw. Es werden an der Datei Kollegen auch arbeiten und das muss ich zusätzlich noch eine Vorschrift schulen. Von daher wir es für andere noch schwieriger.
Verstehe mich nicht falsch ich finde die Lösung super. Aber der Einfachheithalber für die Kollegen, wäre es mir lieber wenn ich nur eine Variante hätte.
Ist es daher irgendwie möglich, dass Ganze auf Spalten aufzuteilen?
Also so wie ich das schon angesprochen habe?
Gruß

Schulen? Warum dann nicht mit StandardFktt ...

18.03.2016 13:57:50

Luc:-?

…statt UDF! Schönen Dank für dein Desinteresse! :-[
Luc :-?

Du tust Dir schon schwer, oder?

19.03.2016 14:16:45

Michael

Hi Jens,
Du brauchst ja nur EINE zu verweden, nämlich Kette5, und zwar so:
https://www.herber.de/bbs/user/104468.xlsm
Kette5 funktioniert sowieso mit beliebig vielen Parametern, und die Formel in Tabelle2 verarbeitet vorläufig bis zu 10 Eingabespalten.
Wozu Schulung? Kette5 brauchst Du nur überall in Spalte Q zu verwenden...
Übrigens funktioniert Luc:-?s Formel für die ursprüngliche Frage ausgezeichnet - wie zu erwarten.
Schöne Grüße,
Michael

Beliebteste Forumthreads (12 Monate)

Verkettungsfunktion über Kriterium

Forumthread: Verkettungsfunktion über Kriterium

8 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread