Erstellung einer Liste über mehrere Tabellenblätter

[Rabe]

Hi,

Ich habe Dein Makro etwas verkürzt:

und hier das Makro unter Ausnutzung der intelligenten Tabellen:

Private Sub CommandButton1_Click()
   Dim loErste As Long
   
   loErste = Worksheets("CIS Code").Cells(Rows.Count, 14).End(xlUp).Row + 1      'erste freie Zelle in Spalte N (14) 
   
   Range("M" & loErste).FormulaLocal = "=SVERWEIS($D$1;Tabelle3;2;0)"
   Range("N" & loErste).FormulaLocal = "=SVERWEIS($D$1;Tabelle3;3;0)"
   Range("O" & loErste).FormulaLocal = "=SVERWEIS($D$1;Tabelle3;4;0)"
   Range("P" & loErste).FormulaR1C1 = "=IF(R2C4="""",0,R2C4)"
   Range("Q" & loErste).FormulaR1C1 = "=IF(R2C5="""",0,R2C5)"
   Range("R" & loErste).FormulaR1C1 = "=IF(R2C6="""",0,R2C6)"
   Range("S" & loErste).FormulaR1C1 = "=IF(R2C7="""",0,R2C7)"
   '    Range("S" & loerste).FormulaLocal = "=WENN($G$2="";0;$G$2)" 
   Range("T" & loErste).FormulaLocal = "=SVERWEIS($D$3;Tabelle2;2;0)"
   Range("U" & loErste).FormulaLocal = "=SVERWEIS($D$4;Tabelle1[[#Alle];[Anlagenbezeichnung - 21-23]:[3]];2;0)"
   Range("V" & loErste).FormulaLocal = "=SVERWEIS($D$4;Tabelle1[[#Alle];[Anlagenbezeichnung - 21-23]:[3]];3;0)"
   Range("W" & loErste).FormulaLocal = "=SVERWEIS($D$4;Tabelle1[[#Alle];[Anlagenbezeichnung - 21-23]:[3]];4;0)"
   Range("Z" & loErste).FormulaLocal = "=SVERWEIS($D$5;Tabelle5[[Baugruppenbezeichnung - 26-28]:[3]];2;0)"
   Range("AA" & loErste).FormulaLocal = "=SVERWEIS($D$5;Tabelle5[[Baugruppenbezeichnung - 26-28]:[3]];3;0)"
   Range("AB" & loErste).FormulaLocal = "=SVERWEIS($D$5;Tabelle5[[Baugruppenbezeichnung - 26-28]:[3]];4;0)"
   
   Range("M" & loErste & ":AB" & loErste).Value = Range("M" & loErste & ":AB" & loErste).Value 'Formeln durch Werte ersetzen 
   'oder 
'   Range("M" & loErste & ":AB" & loErste).Copy                                   'Formeln durch Werte ersetzen 
'   Range("M" & loErste).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
'        :=False, Transpose:=False 
'        Application.CutCopyMode = False 
End Sub

[schauan]

Hallöchen,

hier wäre noch der Formelansatz für die zweispaltige Nummernangabe

Arbeitsblatt mit dem Namen 'CIS Code'
	U	V	W	X	Y
18	Anlage			Anlagen- nummer
19	A	L	T	0	1
20	A	V		0	2
21	L	L		0	3
22	A	L		0	1

Zelle	Formel
U19	=SVERWEIS($D$4;Anlagenbezeichnung!$A$3:$D$116;2;FALSCH)
V19	=SVERWEIS($D$4;Anlagenbezeichnung!$A$3:$D$116;3;FALSCH)
W19	=SVERWEIS($D$4;Anlagenbezeichnung!$A$3:$D$116;4;FALSCH)
X20	{=WENNFEHLER(VERWEIS(2;1/($U$19:U19&$V$19:V19=U20&V20);$X$19:X19);GANZZAHL((MAX($X$19:X$19)*10+MAX(WENN($X$19:X19=MAX($X$19:X19);$Y$19:Y19;""))+1)/10))}
Y20	{=WENNFEHLER(VERWEIS(2;1/($U$19:U19&$V$19:V19=U20&V20);$Y$19:Y19);REST((MAX($X$19:X19)*10+MAX(WENN($X$19:X19=MAX($X$19:X19);$Y$19:Y19;""))+1);10))}
X21	{=WENNFEHLER(VERWEIS(2;1/($U$19:U20&$V$19:V20=U21&V21);$X$19:X20);GANZZAHL((MAX($X$19:X$19)*10+MAX(WENN($X$19:X20=MAX($X$19:X20);$Y$19:Y20;""))+1)/10))}
Y21	{=WENNFEHLER(VERWEIS(2;1/($U$19:U20&$V$19:V20=U21&V21);$Y$19:Y20);REST((MAX($X$19:X20)*10+MAX(WENN($X$19:X20=MAX($X$19:X20);$Y$19:Y20;""))+1);10))}
X22	{=WENNFEHLER(VERWEIS(2;1/($U$19:U21&$V$19:V21=U22&V22);$X$19:X21);GANZZAHL((MAX($X$19:X$19)*10+MAX(WENN($X$19:X21=MAX($X$19:X21);$Y$19:Y21;""))+1)/10))}
Y22	{=WENNFEHLER(VERWEIS(2;1/($U$19:U21&$V$19:V21=U22&V22);$Y$19:Y21);REST((MAX($X$19:X21)*10+MAX(WENN($X$19:X21=MAX($X$19:X21);$Y$19:Y21;""))+1);10))}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

[iDiddi]

Hallo Leute, hier bin ich auch mal wieder. Ich muss erst einmal ein riesengroßes Dankeschön aussprechen, zum einen für die Glückwünsche, zum anderen für das was ihr bisher geleistet habt. Das ist der Wahnsinn. Vor allem danke an Rabe der sich so viel Arbeit gemacht hat und die Tabellen sogar formatiert hat. 
Ein bissel weiter bin ich mittlerweile auch. Ich habe die Eingabemaske ein wenig überarbeitet, habe noch eine Suchabfrage integriert,  und das Eintragen bis zu der Stelle funktioniert sehr gut. Bei genaueren Betrachten ist mir auch aufgefallen, dass die Anlagennummer nicht automatisch vergeben werden kann, da es dafür keine eindeutige Definition gibt. Diese gibt der Anwender selbst ein. 

Nun hänge ich noch an zwei anderen Problemen. Zum einen möchte ich dass die Raumnummer immer ein vierstelliges Format, die Anlagennummer immer zweistellig ist. Sollte der Bearbeiter ein anderes Format eingeben soll er darauf hingewiesen werden, dass es falsch ist. Ich kenne nur die Möglichkeit dies über ein Textfeld zu realisieren. Geht das auch noch anders? bei Raumnummer sollte nur der Hinweis kommen "Raumnummer muss vierstellig sein" bei, der Anlagennummer würde es reichen, wenn aus einstelligen Ziffern zweistellige werden (z.B. aus 4 wird 04). Das hatte ich im ersten Schritt über Format versucht zu realisieren, allerdings wird beim Eintragen der Anlagennummer nur das tatsächlich in der Zelle eingetragene übernommen und die vorangestellte 0 nicht beachtet. 

Problem 2 ist für mich weitaus komplexer und dreht sich um die automatische Vergabe der Baugruppennummer. Anhand der Zellen M bis AB soll eine eindeutige dreistellige Nummer in die Zellen AC-AE eingetragen werden. Theoretisch sollten Zellen U-AB auch ausreichen um die Nummer zu bestimmen, aber ich glaube es wird kein großer Unterschied sein ob bei M oder bei U angefangen wird. 

So, dann werde ich mich für heute ersteinmal wieder verabschieden und wünsche allen einen schönen Tag. 
Grüße, Diddi

[Rabe]

Hi,

wie ich schon im Beitrag #4 geschrieben habe:
Außerdem solltest Du Dir noch eine immer gleiche Spalten-Sortierung überlegen: zuerst Kennziffer-Spalte, dann Bezeichnung oder anders rum.
Denn im Blatt BMK ist es anders, wie in den anderen.

zum Suchen:
das mit dem roten Rand gefällt mir gut, wie wird dieser wann wieder rückgesetzt?

Nun zu Deinen Problemen:

Nun hänge ich noch an zwei anderen Problemen. Zum einen möchte ich dass die Raumnummer immer ein vierstelliges Format, die Anlagennummer immer zweistellig ist. Sollte der Bearbeiter ein anderes Format eingeben soll er darauf hingewiesen werden, dass es falsch ist.

Dazu nimmst Du die Datengültigkeits-Prüfung:
   
   

Und für die Anlagennummer analog eben 2-stellig!

Problem 2 ist für mich weitaus komplexer und dreht sich um die automatische Vergabe der Baugruppennummer. Anhand der Zellen M bis AB soll eine eindeutige dreistellige Nummer in die Zellen AC-AE eingetragen werden. Theoretisch sollten Zellen U-AB auch ausreichen um die Nummer zu bestimmen

und wie soll sich die Baugruppennummer 3stellig aus 16 oder 8 Spalten zusammensetzen?

[iDiddi]

Hi,

ich weiß, die Blätter ab Datenpunktart habe ich noch nicht angefasst. Da es sich dabei um mein Gewerk handelt, folgen die erst, wenn ich den Rest fertig habe und die Kollegen schon mit der Liste arbeiten können. 

Die roten Umrandungen werden übrigens immer  beim Initialisieren einer neuen Suchanfrage zurückgesetzt. Also wie im Modul 1 zu sehen die ersten Zeilen: 

Code:

xy = Worksheets("Baugruppenbezeichnung").Index
Sheets(xy).Select
    Cells.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Cells(1, 1).Select

Das mit der Datengültigkeitsprüfung ist ja einfach. Darauf wäre ich nicht gekommen. Danke für den Hinweis. 

Die Baugruppennummer ist eigentlich relativ simpel eine fortlaufende Nummer in Abhängigkeit der Zellen (im Idealfall A bis) T-AB. Jede Baugruppennummer kann es nur einmal geben. Als abgekürztes Beispiel nur auf Gewerk|Anlagenart|Anlagennummer|Baugruppenbezeichnung|Baugruppennummer:
L|RLT|01|FIL|001 --> erster Filter der RLT Anlage 01 des Gewerk Lüftung
L|RLT|01|FIL|002 --> zweiter Filter der RLT Anlage 01 des Gewerk Lüftung
L|RLT|01|SET|001 --> erster Temperaturfühler der RLT Anlage 01 des Gewerk Lüftung
L|RLT|01|SET|002 --> zweiter Temperaturfühler der RLT Anlage 01 des Gewerk Lüftung
L|RLT|02|FIL|001 --> erster Filter der RLT Anlage 02 des Gewerk Lüftung
L|RLT|02|SET|001 --> erster Temperaturfühler der RLT Anlage 01 des Gewerk Lüftung
L|RLT|02|SET|002 --> zweiter Temperaturfühler der RLT Anlage 01 des Gewerk Lüftung
L|RLT|01|SET|003 --> dritter Temperaturfühler der RLT Anlage 01 des Gewerk Lüftung

Ich hoffe du weißt wie ich es meine. 
Grüße, Diddi

[iDiddi]

So, nach gefühlten Ewigkeiten (andere Projekte, Urlaub, Krankheit) bin ich auch wieder mal an der Liste und will sie eigentlich auch langsam mal zum Abschluss bringen. Ich bin so weit gekommen, dass ich zumindest per Formeln das zählen der Baugruppennummer (Spalte BB) hinbekommen habe. Was mir nun noch fehlt wäre sind zwei Dinge:

1. Die Spalte BB muss aufgeteilt werden und in die Spalten AC, AD, AE, eingetragen werden. Problem ist hierbei, dass ich zwar die Formatierung der Spalte BB angepasst habe und dort drei Zahlen angezeigt werden. Die vorgestellten 0 werden bei der Teil(BB)-Funktion leider nicht beachtet. Die einzige Abhilfe die mir einfällt wäre die Teil(BB)-Funktion mit Hilfe der Länge(BB)-Funktion zu koppeln und anhand der Länge die Zellen zu füllen. Dies habe ich in Zeile 39 gemacht. Zufrieden bin ich damit allerdings nicht wirklich. Sicher gibt es eine elegantere Lösung auf die ich aber gerade nicht komme. 

2. Großartig wäre es wenn das Ausfüllen der Zellen AC, AD und AE ebenfalls direkt per VBA (über den Button "CIS Code schreiben") mit erledigt werden würde. Aber dazu fehlt mir noch so viel, dass ich auch hierbei hoffe, auf euer Wissen zurück greifen zu können. 

Viele Grüße,
Diddi

[Rabe]

Hi Diddi,

1. Sicher gibt es eine elegantere Lösung auf die ich aber gerade nicht komme. 
2. Großartig wäre es, ...

hier die schnelle Formel-Lösung für Frage 1:

CIS Code

	AC	AD	AE
19	0	0	1

verwendete Formeln
Zelle	Formel	Bereich	N/A
AC19	=TEIL(TEXT($BB19;"000");1;1)
AD19	=TEIL(TEXT($BB19;"000");2;1)
AE19	=TEIL(TEXT($BB19;"000");3;1)

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.08 einschl. 64 Bit

Frage 2 inklusive Berechnung/Eintragen von AY und BB (AZ und BA brauchst Du sowieso nicht mehr):

   Range("AB" & loErste).FormulaLocal = "=SVERWEIS($D$8;Tabelle5[[Baugruppenbezeichnung - 26-28]:[3]];4;0)"

  Range("AC" & loErste).FormulaLocal = "=TEIL(TEXT($BB" & loErste & ";""000"");1;1)"

  Range("AD" & loErste).FormulaLocal = "=TEIL(TEXT($BB" & loErste & ";""000"");2;1)"

  Range("AE" & loErste).FormulaLocal = "=TEIL(TEXT($BB" & loErste & ";""000"");3;1)"

  Range("AY" & loErste).FormulaLocal = "=M" & loErste & "&N" & loErste & "&O" & loErste & "&P" & loErste & "&Q" & loErste & "&R" & loErste & "&S" _

  & loErste & "&T" & loErste & "&U" & loErste & "&V" & loErste & "&W" & loErste & "&X" & loErste & "&Y" & loErste & "&Z" & loErste & "&AA" & loErste _

  & "&AB19"

  Range("BB" & loErste).FormulaLocal = "=ZÄHLENWENN($AY$19:AY" & loErste & ";AY" & loErste & ")"

  

  Range("M" & loErste & ":BB" & loErste).Value = Range("M" & loErste & ":BB" & loErste).Value 'Formeln durch Werte ersetzen 

[iDiddi]

Hallo Ralf, 

auch wenn ich mir die 2. Lösung bisher noch nicht angeschaut habe und eher zufällig entdeckt habe (leider kam keine EMail das im Thema was bearbeitet wurde) möchte ich dir ganz recht herzlich für deine geleistete Arbeit danken. Die Tipps, Hinweise und Lösungsvorschläge waren alle überragen und sogar für mich verständlich. 


Grüße, Diddi

[iDiddi]

Also Ralf noch einmal riesen Dank. Das funktioniert alles bestens. 

Ein Problem habe ich gester noch festgestellt, was ich gar nicht weiß, wie ich es angehen soll. Da die Tabelle über mehrere Planungsphasen genutzt wird, ist es natürlich auch möglich das Sensoren aus der Vorplanung bei der Ausführungsplanung nicht mehr genutzt werden. Damit keine toten Einträge in der Liste stehen, wäre es schön wenn man diese einfach löschen könnte. Das Problem dabei ist, das sobald ein Eintrag gelöscht wird, wird die komplette Tabelle neu berechnet. Gibt es irgend eine Möglichkeit, dass ausgefüllte Zellen nicht noch einmal angefasst werden? Wichtig wäre in dem Zusammenhang auch noch, dass eine einmal vergebene Baugruppennumemrierung nicht ein zweites mal vergeben werden darf, auch wenn diese weiter vorn wieder gelöscht wurde. Sprich hat man "....SET001" bis "...SET010", löscht den "...SET008" muss der nächste Temperatursensor trotzdem "...SET011" heißen. 
Ich glaube mit meiner bisherigen Herangehensweise ist das aber so gar nicht zu vereinbaren, zumindest wüsste ich nicht wie. Wahrscheinlich ist das einfachste die Zellen nicht zu löschen sondern nur durch zu streichen. Wenn trotzdem jemand eine Idee hat, immer her damit! 

Grüße Diddi