Herauslesen und Verketten

[Raoul21]

Hi

darf ich mich hier mit einer Nebenfrage einmischen?

habe eine Function namens "Palette(Code)" geschrieben, die, unter Berücksichtigung der verbundenen Zellen, die Paletten mit gegebenen Code zurückgeben.

Code:

Option Explicit

Function Palette(Code As String) As String
   Dim FundZelle As Range, FundAdr1 As String
   With Tabelle1.Range("A1:B13")
      Set FundZelle = .Find(Code, LookIn:=xlValues, lookat:=xlWhole)
      If Not FundZelle Is Nothing Then
         FundAdr1 = FundZelle.Address
         Do
            Palette = Palette & FundZelle.Offset(, -1).MergeArea(1) & ", "
            Set FundZelle = .FindNext(FundZelle)
         Loop While FundZelle.Address <> FundAdr1
         Palette = Left(Palette, Len(Palette) - 2)
      End If
   End With
End Function

Sub Testsuche()
   Dim a As String
   a = Palette("aaa")
   Debug.Print a
End Sub

Palette("aaa") ergibt "1, 4" bei Aufruf vom Sub "Testsuche", aber bei Verwendung in einer Zelle kommt "#WERT".

Warum? Was muss man da ändern?

Gruss, Raoul

[00202]

Warum? Was muss man da ändern?

Hallo, :19:

"FindNext" zickt in UDFs ordentlich rum - rufe statt der Zeile mit ".FindNext..." einfach nochmal ".Find..." auf - mit "After:=..." der letzten gefundenen Zelle: :21:

Code:

Set FundZelle = .Find(What:=Code, LookIn:=xlValues, LookAt:=xlWhole, After:=FundZelle)

[Albina_96]

Hallo nochmal an alle,

also erst einmal ein riesengroßes Dankeschön für all die hilfreichen Antworten!

@shift-del: Ich habe deine Formel für meine Tabelle angewandt und es funktioniert SUPER!
Genau das, was ich haben wollte. Dankeschön!
Jetzt bin ich so sehr davon begeistert, dass ich noch einen Schritt weiter gehen wollte und es leider nicht ganz so geklappt hat, wie ich es mir vorgestellt hab.
Wenn man in der Tabelle noch eine Spalte "Container" hätte, welche jeder Palette einen von mehreren Containern zuordnet, aber wiedereinmal ein Container zu mehreren Paletten zugeordnet werden kann... wie würde die Formel dazu aussehen?

Die Datei hängt wiedermal dabei mit meinem Fehlversuch. :(

@alle, die PowerQuery angesprochen haben: auch an euch danke. Da ich hier nichts auf den Arbeitsrechner runterladen will bzw. darf ohne vorher die Bestätigung von oben eingeholt zu haben, werde ich damit erstmal warten, aber auf jeden Fall mal ansprechen. Ich hab mich dazu ein wenig belesen und es klingt auf jeden Fall sehr nützlich und vor allem hilfreich.

VG Albina

[shift-del]

Meinst du das so?

Arbeitsblatt mit dem Namen 'Tabelle1'
	J	K	L	M	N	O	P	Q	R	S	T	U	V
14	Container	Paletten			Container:	1	2	3	4	5	6	7
15	a	1,2,3,4,5,6				1	2	3	4	5	6
16	b	7,7,7				7	7	7
17	c	8,9,10				8	9	10

Zelle	Formel
J15	=WENNFEHLER(INDEX($C$2:$C$13;VERGLEICH(0;ZÄHLENWENN($J$14:J14;$C$2:$C$13);0));"")
K15	=WECHSELN(GLÄTTEN(O15&" "&P15&" "&Q15&" "&R15&" "&S15&" "&T15&" "&U15);" ";",")
O15	=WENNFEHLER(INDEX($A:$A;AGGREGAT(15;6;ZEILE($C$2:$C$13)/($C$2:$C$13=$J15);O$14));"")

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg

[Jockel]

Dabei ist es sooo einfach mit PQ... Ohne Formeln ohne Formelvorrat, dynamisch ohne VBA - egal...

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I	J	K
1	Pallete	Code	Container			Container	Palette(n)		Code	Paletten	Container
2	1	aaa	a			a	1, 2, 3, 4, 5, 6		aaa	1, 4	a, a
3	2	abb	a			b	7, 7, 7		aba	3, 8	a, c
4	3	aba	a			c	8, 9, 10		abb	2	a
5	4	aaa	a						abc	5, 9	a, c
6	5	abc	a						abd	6	a
7	6	abd	a						cde	7	b
8	7	cde	b						cdf	7	b
9	7	cdf	b						cdg	7	b
10	7	cdg	b						dge	10	c
11	8	aba	c
12	9	abc	c
13	10	dge	c

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[RPP63]

Tach!
Ich weiß wirklich nicht, warum PQ-"Lösungen" verstärkt mit der Aura des Geheimnisvollen angepriesen werden!
Entweder ich gebe eine komplette Lösung oder ich lasse es!

Stell Dir vor, jemand bittet um eine Lösung und ich schreibe:
"Mit VBA total easy!"
Als "Lösung" biete ich dann einen Screenshot nach Durchlauf des Makros an …

Kopfschüttelnd,
Ralf

[Jockel]

Tach, das ist nicht geheimnisvoll, sondern voll einfach. Und hier so sinnvoll wie Formellösungen mit der XL 365 er Insider Version... ich möchte Interesse wecken...  .... für den TE eher nicht. Wer widererwarten eine Frage dazu hat, kann sie gerne stellen, aber bitte nicht frotzeln...

P.S. dieses Thema ist sooft hier behandelt worden... Aber du kannst weiter was auch immer schütteln...

... sich wundernd...

[Raoul21]

Hallo Case,

danke. Seltsame Regel. Keinen Hinweis darauf zu finden, ausser man sucht direkt danach. Sehr heimtückisch.



Hallo Albina

nun konnte ich meine Funktion mit Cases Hilfe fertigstellen. Füge folgenden Code in ein Modul in Deine erste Beispieltabelle ein und verwende die Funktion wie folgt:

Schreibe in eine Zelle Deiner Wahl:
=Palette("aaa") oder als Bezug zB =Palette(B15)
liefert das gewünschte Ergebnis.

Für die Formel sind verbundene Palettenzellen, wie sie in Deinem Beispiel auch vorkommen, kein Problem.

OK, und hier der Code für die Funktion namens "Palette":

Code:

Function Palette(Code As String) As String
   Dim FundZelle As Range, FundAdr1 As String
   With Tabelle1.Range("A1:B13")
      Set FundZelle = .Find(Code, LookIn:=xlValues, lookat:=xlWhole)
      If Not FundZelle Is Nothing Then
         FundAdr1 = FundZelle.Address
         Do
            Palette = Palette & FundZelle.Offset(, -1).MergeArea(1) & ", "
            Set FundZelle = .Find(Code, after:=FundZelle)
         Loop While FundZelle.Address <> FundAdr1
         Palette = Left(Palette, Len(Palette) - 2)
      End If
   End With
End Function

Das ist nur eine zusätzliche Möglichkeit. Denn erstens ist die Aufganbenstellung inzwischen etwas erweitert worden, und zweitens wurde eine unschlagbar einfach anzuwendende Formellösung präsentiert.

Gruß,
Raoul

[Raoul21]

Hallo Albina,

falls Du mit meinem Makro für eine Tabellenfunktion, das ich in zuvor geposteten Beitrag #18 präsentiert habe, etwas anfangen kannst, dann kann ich vielleicht auch Deine in #15 gewünschte Container-Funktion in einer Erweiterung berücksichtigen.

Gruss, Raoul.

[Albina_96]

Hallo zusammen, 

Nun ist etwas viel Zeit seit meiner letzten Meldung vergangen und das tut mir schrecklich Leid. Da war leider zu viel los die letzten Wochen. 

Aber zurück zum Thema: So ungefähr wie Jockel es gemacht hat, hätte ich es gern, nur ohne, dass sich die Containernummern wiederholen. Das war auch das Problem bei mir. Da in der originalen Datei sowohl die Codes als auch die Containernummern öfter als nur 2-3 mal auftauchen, sieht es schon ziemlich seltsam aus, wenn ein- und dieselbe Containernummer 10 mal in einer Zelle vorkommt.
Aber das geht schonmal in die richtige Richtung. Ich hoffe, ihr habt noch einen Vorschlag für mich?  
@Raoul21 Ich würde deine Lösung morgen sofort mal ausprobieren und mich dann wieder melden. 

Nochmal danke für die Mühe an alle!

VG Albina