Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Formelerweiterung nach einfügen einer Spalte

Formelerweiterung nach einfügen einer Spalte
11.04.2016 09:01:57
Sascha
Hallo,
ich habe folgendes Problem.
Ich habe mit der Funktion (VERKETTEN(WENN(B2="x";B$1&TEXT("; ";););WENN(C2="x";C$1&TEXT("; ";););WENN(D2="x";D$1&TEXT("; ";)))) eine Verkettung gemacht und möchte nun in einer beliebigen Spalte zwischen B und D eine Spalte einfügen.
Dabei soll sich die Formel mit Hilfe eines Makros automatisch erweitern.
Anbei ist eine Beispieldatei, die das Problem erläutern soll. https://www.herber.de/bbs/user/104879.xlsx
In der Beispieldatei ist zu erkennen, dass ich das für jede Farbe so gemacht habe. Gibt es dort eine Möglichkeit anhand von Farbenerkennung nur ein einziges Makro zu erstellen, dass die richtige Formel aktualisiert anhand der Farbe?
Vielen Dank im Voraus.
Sascha

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Frage an Luc:-? könnte das evtl. VJoni() ? owT
11.04.2016 15:46:13
...
Gruß Werner
.. , - ...

VJoni() nicht, aber VJoin, Werner! ;-)
11.04.2016 23:05:52
Luc:-?
Und zwar auch mit nur einer Fml, die von N2 in alle Ergebniszellen kopiert wdn kann:
 ABCDEFGHIJKLMNOPQ
1 3200 Druckgaspackungen3201 entzündbare Arbeitsstoffe3202 organische PeroxideReizungenVergiftungenVerätzungNassspritzkabineTrockenspritzkabineLaborabzugVon Anfang an sicher arbeiten (BG Chemie, A005)Übersicht PSA (PSA-Aushänge)Aushang HautschutzplanZusammenfassung grauZusammenfassung gelbZusammenfassung blauZusammenfassung rot
2Sascha X  X  X    3201 entzündbare ArbeitsstoffeVergiftungenTrockenspritzkabine 
3Moritz X XXXX  XXX3201 entzündbare ArbeitsstoffeReizungen; Vergiftungen; VerätzungNassspritzkabineVon Anfang an sicher arbeiten (BG Chemie, A005); Übersicht PSA (PSA-Aushänge); Aushang Hautschutzplan
4Joschua   XXX X XXX Reizungen; Vergiftungen; VerätzungTrockenspritzkabineVon Anfang an sicher arbeiten (BG Chemie, A005); Übersicht PSA (PSA-Aushänge); Aushang Hautschutzplan
5N2[:Q4]: {=VJoin(WENN(INDEX(($B2:$D2;$E2:$G2;$H2:$J2;$K2:$M2);;;SPALTE(A2))="x";INDEX(($B$1:$D$1;$E$1:$G$1;$H$1:$J$1;$K$1:$M$1);;;SPALTE(A2));"");"; ";-1)}
Feedback nicht unerwünscht! Gruß, Luc :-?
Besser informiert mit …

Anzeige
AW: VJoni() nicht, aber VJoin, Werner! ;-)
12.04.2016 09:15:40
Sascha
Hallo Luc:-?
Erstmal vielen vielen Dank!!
Jedoch gibt er mir in N2 bis Q4 nur #Name? raus. Was mache ich falsch?
Kannst du mir auch kurz erklären was Vjoin ist? Ich habe noch nie davon gehört.
Auch an dich Daniel vielen Dank!
Gruß Sascha

Bin schon ein Stück weiter ;)
12.04.2016 10:28:55
Sascha
Hallo nochmal,
ich habe mal selber noch weiter herumgeforscht und habe verstanden was vjoin ist und habe es erfolgreich anwenden können. Jedoch in einer abgewandelten Form.
meine Funktion in N2 lautet: =Vjoin(B2:D2;"X";$B$1:$DK$1)
Somit habe ich natürlich in O2,P2 und Q2 eine andere Funktion.
Mein Makro lautet:
Public Function Vjoin(Bereich_Kriterium, Kriterium, Bereich_Verketten)
Dim mydic As Object
Dim L As Long
Set mydic = CreateObject("Scripting.Dictionary")
For L = 1 To Bereich_Kriterium.Count
If Bereich_Kriterium(L) = Kriterium Then
mydic(L) = Bereich_Verketten(L)
End If
Next
Vjoin = Join(mydic.items, ", ")
End Function

Wie muss ich dieses ändern, sodass ich deine Formel anwenden kann?
Gruß
Sascha

Anzeige
AW: Bin schon ein Stück weiter ;)
12.04.2016 13:14:14
Sascha
Und nochmal Hallo,
gibt es vielleicht auch eine Möglichkeit, dass das Ergebnis in N2 nicht nur aneinander gekettet werden kann, sondern zusätzlich einen Absatz zwischen den einzelnen aneinander geketteten Zellen setzt?
Nochmals vielen Dank und schöne Grüße aus dem sonnigen Hamburg.
Sascha

AW: Bin schon ein Stück weiter ;)
12.04.2016 13:30:42
Daniel
Hi
verwende statt des Kommas das Zeichen mit dem ASCII-Code 10 als Trennzeichen (CHR(10))
dies ist auch in der Systemkonstanten vbLF hinterlegt.
in der Zellformatierung musst du ggf noch das Häkchen bei "Zeilenumbruch" setzen, wenn Excel das nicht schon automatisch macht.

Anzeige
AW: Bin schon ein Stück weiter ;)
12.04.2016 14:18:11
Sascha
Hallo Daniel,
hat funktioniert! Vielen Dank.
Ihr seid echt unglaublich!
Gruß
Sascha

So etwas liebe ich nicht gerade, ...
12.04.2016 15:37:38
Luc:-?
…Sascha,
an XlFktt kannst du ja auch nicht nach Belieben rumverändern. Eine eigene UDF kannst du aber gern verwenden (toll und Lob, dass du dich da reingedacht hast, und sogar mit einem Dictionary - meine verwendet keins! ;-]), nur sollte die dann auch etwas anders (KlBst statt GrBst reicht nicht!) heißen, damit kein Wirrwarr entsteht.
Meine AW war an Werner gerichtet, weshalb ich keinen Link-Hinweis geschrieben hatte, denn der kennt die ja. Die letztveröffentlichte Version meiner UDF ist 1.4, die aber nur in einigen hochgeladenen BspDateien zu finden ist (inkl KurzErläuterung). Bei RECHERCHE im Archiv findest du aber genug Hinweise und Links auf solche Dateien. Die Original-UDF (Vs1.4) ist übrigens deutlich länger als deine und mit der bisher unveröffentlichten Vs1.5 wieder etwas länger, was bedeutet, sie kann noch mehr als ihre Vorgänger.
Gruß, Luc :-?

Anzeige
AW: aber so finde ich es schneller wieder ;-) owT
12.04.2016 09:16:08
...
Gruß Werner
.. , - ...

So, so...! ;-) owT
14.04.2016 13:25:06
Luc:-?
:-?

AW: Formelerweiterung nach einfügen einer Spalte
11.04.2016 16:52:40
Daniel
Hi Sascha
mit folgenem Makro könntest du dir die Formeln in die Zellen schreiben lassen:
das Marko muss jedesmal ausgeführt werden, wenn du in der Zeile 1 Spalten hinzufügst, löschst oder umfärbst.
die Spalten für die Zusammenfassung müssen mit dem Text "Zusammenfassung" beginnen um als solche erkannt zu werden.
Sub FormelnAktualisieren()
Dim dic As Object
Dim Zelle As Range
Dim AnzZeilen As Long
Dim ID As Long
Set dic = CreateObject("Scripting.Dictionary")
AnzZeilen = WorksheetFunction.CountA(Columns(1))
For Each Zelle In Rows(1).SpecialCells(xlCellTypeConstants, 2)
ID = Zelle.Interior.Color
If Zelle.Value Like "Zusammenfassung*" Then
If dic.exists(ID) Then
Zelle.Offset(1, 0).Resize(AnzZeilen).FormulaR1C1 = "=Mid(" & Mid(dic(ID), 2) & ",3,9999)"
Else
Zelle.Offset(1, 0).Resize(AnzZeilen).ClearContents
End If
Else
dic(ID) = dic(ID) & "&IF(RC" & Zelle.Column & "=""x"",""; ""&R1C" & Zelle.Column & ","""")"
End If
Next
End Sub
Gruß Daniel
Anzeige

325 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige