Microsoft Excel

Herbers Excel/VBA-Archiv

Schüler-Klassen-Listen

Betrifft: Schüler-Klassen-Listen von: Guse
Geschrieben am: 05.11.2014 12:06:49

Hallo,

Ich habe ein Registerblatt ( Eingabe) in dem alle Kinder mit Namen Vornamen Geb Ort Str etc eingetragen sind.
ZUSATZ !!!
Die Registerkarte EINGABE wird automatisch via VBR nach Klassen und NAMEN sortiert.

Insgesamt sind es 700 Kinder. Ebenfalls trage ich in dieser Registerkarte ein in welcher Klasse die Kinder sind.

In einer weiteren Registerkarte ( Klassenliste ) Habe ich unter anderem ein Dropdown mit allen Klassen. In der Tabelle darunter habe ich mit WENN Funktionen die Bezüge hergestellt.

Wähle ich jetzt z.b. in dem Drop Down Klasse 1a aus, zeigt er mir die Kinder mit allem was ich in der Klassenliste brauche an. Da er sich die daten aus der Eingabe holt und dieser dort oben Stehen stehen sie auch bei der Klassenliste oben. anders ist es wenn ich Klasse 2a auswähle, da entstehen erstmal ca 80 leerzeilen. Also habe ich versucht via Autofilter die Leerzeilen zu entfernen. Klappt auch.

jetzt zum Problem !!!!

jedesmal wenn der Filter aktiviert wird und zeilen gelösch/ ausgeblendet werden, verschiebt er die Formeln in den ausgeblendeten feldern nach unten. Wenn man dann z.b. Klasse 5a ausrufen möchte findet er keine daten da die formeln schon an dem eigentlichen Daten vorbei sind.

Das ist echt blöd zu erklären. Ich hoffe irgend wer versteht das.

Hat einer eine Idee wie ich das Lösen kann oder eine Idee vor ne komplett andere Variante.

Danke


  

Betrifft: AW: Schüler-Klassen-Listen von: Michael
Geschrieben am: 05.11.2014 12:32:54

Hallo Guse!

Wenn ich Deine Frage richtig verstehe geht es Dir um Folgendes:

Du hast eine Datenbasis (Schülerliste) die Du nach einem gewissen Kriterium (Klassenbezeichnung) gefilter anzeigen willst, also bspw. aus 700 Schülern jene 15 Schüler die dem Kriterium "5B" entsprechen und deren Daten.

Dafür könntest Du doch einen ganz simplen Weg über eine Pivot-Tabelle gehen, die auf Deine Schülerliste zugreift. Ist sogar noch wartungsfreier als mit Wenn-Bezügen herumzuwerken.


  

Betrifft: AW: Schüler-Klassen-Listen von: Guse
Geschrieben am: 05.11.2014 17:29:30

Hallo Michael,

ja das will ich !!! Klingt gut was du vorschlägst doch leider habe ich davon noch nie was gehört !!!
Hast du ein kleine Anleitung oder Beispiel ???


  

Betrifft: per Index und Vergleich von: Rudi Maintaire
Geschrieben am: 05.11.2014 12:42:32

Hallo,
ein vereinfachtes Bsp.

ABCDEFGH
1KlasseVNNN  1bVNNN
21aVN01NN01   VN08NN08
31aVN02NN02   VN09NN09
41aVN03NN03   VN10NN10
51aVN04NN04   VN11NN11
61aVN05NN05   VN12NN12
71aVN06NN06   VN13NN13
81aVN07NN07     
91bVN08NN08     
101bVN09NN09     
111bVN10NN10     
121bVN11NN11     
131bVN12NN12     
141bVN13NN13     
152aVN14NN14     
162aVN15NN15     
172aVN16NN16     
182aVN17NN17     
192aVN18NN18     

ZelleFormel
G2:Gx=WENN(ZEILE($A1)<=ZÄHLENWENN($A:$A;$F$1);INDEX(B:B;VERGLEICH($F$1;$A:$A;)+ZEILE(A1)-1);"")
H2:Hx=WENN(ZEILE($A1)<=ZÄHLENWENN($A:$A;$F$1);INDEX(C:C;VERGLEICH($F$1;$A:$A;)+ZEILE(B1)-1);"")


Gruß
Rudi


  

Betrifft: AW: per Index und Vergleich von: Guse
Geschrieben am: 06.11.2014 21:59:37

Hallo Rudi,

ich habe deine Wenn Funktion angewendet. Klappt perfekt !!!!

Nur ein Fehler entsteht und ich finde nicht heraus warum.
Egal welche Klasse ich in dem Dropdown auswähle er übernimmt aus der Eingabe KArteikarte nicht das letzte Kind/Datensatz. Bei allen vorherigen Kindern macht es er richtig, NUR der letzte nicht. Es ist die gleiche Formel drin

Lösung ???

Sex Name Vornamen
w Abo Rwan
m Alam Shihab
w Al-Sayhan Maha
m Ates Emin
w Bäker Fabienne-Franziska
m Balhas Ali
w Batmaca Zaide-Nur
w Bayram Rihan
m Berjaoui Ahmad
w Chteiwiah Siham
w El Nomeiri Malak
m El-Touki Mohammad

Formel aus erster Zeile ( A7 )
=WENN(ZEILE('[Schülerdatei Entwurf 2014 - 04.11.14.xlsm]Eingabe'!$B2)< =ZÄHLENWENN('[Schülerdatei Entwurf 2014 - 04.11.14.xlsm]Eingabe'!$B:$B;$D$1); INDEX('[Schülerdatei Entwurf 2014 - 04.11.14.xlsm]~f~

Formel aus letzter Zeile ( A29)
~f~=WENN(ZEILE('[Schülerdatei Entwurf 2014 - 04.11.14.xlsm]Eingabe'!$B24)<=ZÄHLENWENN('[Schülerdatei Entwurf 2014 - 04.11.14.xlsm]Eingabe'!$B:$B;$D$1);INDEX('[Schülerdatei Entwurf 2014 - 04.11.14.xlsm]Eingabe'!C:C;VERGLEICH($D$1;'[Schülerdatei Entwurf 2014 - 04.11.14.xlsm]Eingabe'!$B:$B;)+ZEILE('[Schülerdatei Entwurf 2014 - 04.11.14.xlsm]Eingabe'!C23)-1);"")




  

Betrifft: AW: Schüler-Klassen-Listen von: Crazy Tom
Geschrieben am: 05.11.2014 13:09:13

Hallo

hier eine VBA-Variante

Private Sub ComboBox1_Change()
    Dim Suchergebnis As Range
    Dim firstAddress
    Dim lngZielZeile As Long
    Dim What As String
    Dim Suchbereich As Range
    What = ComboBox1.Text
    If What = "" Then Exit Sub
    Sheets("Klassenliste").Range("A2:G100").ClearContents
    lngZielZeile = 2
    With Sheets("Eingabe")
        Set Suchbereich = .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
            Set Suchergebnis = Suchbereich.Find(What, LookIn:=xlValues, lookat:=xlWhole)
            If Not Suchergebnis Is Nothing Then
                firstAddress = Suchergebnis.Address
                Do
                    Sheets("Klassenliste").Cells(lngZielZeile, 1).Resize(1, 7).Value = _
                        .Cells(Suchergebnis.Row, 1).Resize(1, 7).Value
                    lngZielZeile = lngZielZeile + 1
                    Set Suchergebnis = Suchbereich.FindNext(Suchergebnis)
                Loop While Not Suchergebnis Is Nothing And Suchergebnis.Address <> firstAddress
            End If
    End With
End Sub

in dem Beispiel wird in Spalte A im Blatt Eingabe nach der Klasse gesucht
im Blatt Klassenliste werden zuerst die Einträge in A2 bis G100 gelöscht
und dann die Einträge von Spalte A bis Spalte G ins Blatt Klassenliste kopiert

MfG Tom


  

Betrifft: AW: Schüler-Klassen-Listen von: Guse
Geschrieben am: 06.11.2014 09:35:15

Hallo Crazy Tom,

vielen Dank für deinen VBR Code. Er klappt super bis auf eine kleinigkeit.

Bsp.: in der Eingabe sind 20 Kinder in der Klasse 1a ( Zeile A2 - A22 ). Die nachsten Kinder sind dann z.b Klasse 1b ( 25 Kinder )

Problem: wenn ich in der Registerkarte Klassenliste die Klasse 1b auswähle, holt er sich zwar alle Daten aber packt sich nicht in die erste zubenutzende zeile sondern beginnt da wo sie in der Eingabe auch stehen, also A23 z.b.

Es sollte aber so sein das egal welche Klasse ausgerufen wird immer in der obersten Zeile der Klassenliste der Eintrag beginnt.

Hat einer eine Lösung ????


  

Betrifft: AW: Schüler-Klassen-Listen von: Crazy Tom
Geschrieben am: 06.11.2014 12:36:37

Hallo

das mag ich grade gar nicht glauben
in dieser Codezeile

lngZielZeile = 2

habe ich die Zeile 2 als Startzeile angegeben
und alle weiteren Einträge werden dann eine Zeile tiefer dazu gepackt
wenn die Combobox das nächste mal geändert wird, wird hiermit
Sheets("Klassenliste").Range("A2:G100").ClearContents

der Zellenbereich A2 bis G100 geleert
und der nächste Eintrag findet dann wieder in Zeile 2 statt

MfG Tom


  

Betrifft: AW: Schüler-Klassen-Listen von: Guse
Geschrieben am: 06.11.2014 16:54:06

Hallo Tom,

okay das macht Sinn was Du schreibst!!!!

Dann Stelle ich mal ne blöde frage, muss ich eine Combobox erstellen via USerform oder reicht ein Dropdown ??? oder ist gar beides das selbe ???

Wenn Dropbox reicht wo sage ich ihm wo diese ist im VBR code ???

Vielen Dank nochmal


  

Betrifft: AW: Schüler-Klassen-Listen von: Crazy Tom
Geschrieben am: 06.11.2014 18:12:44

Hallo

am einfachsten ist es mit einer Combobox aus den ActiveX-Steuerelementen
dann kannst du meinen Code 1:1 in das Tabellenblatt kopieren in dem die Combobox ist

MfG Tom


  

Betrifft: AW: Schüler-Klassen-Listen von: Guse
Geschrieben am: 06.11.2014 19:25:42

Hallo Tom,

ich versuche das gleich mal. Falls ich es nicht schaffe melde ich mich noch mal und würde mich freuen weiter Hilfe zuerhalten

Vielen Dank schon mal


  

Betrifft: AW: Schüler-Klassen-Listen von: Guse
Geschrieben am: 06.11.2014 19:39:37

Hallo Tom,

ich versuche das gleich mal. Falls ich es nicht schaffe melde ich mich noch mal und würde mich freuen weiter Hilfe zuerhalten

Vielen Dank schon mal