Kann man die oberste Zeile unterhalb einer Fixierung "ansprechen"

[MisterBurns]

Du hast nichts "falsch" gemacht, du hast schlicht "nichts" gemacht. Der Code an sich steht mal nur da und wenn er nicht manuell (wie zB durch F5) ausgeführt wird, passiert gar nichts. Du hast nun folgende Möglichkeiten:

- Du fügst einen Button im Tabellenblatt ein und verknüpfst den mit dem Makro. Dann musst du nach dem Setzen des Autofilters noch auf den Button klicken, damit der Code ausgeführt wird.

oder

- Schreibe den Code wie folgt

Code:

Private Sub Worksheet_Calculate()
Dim lngRow As Long

  For lngRow = 15 To Cells(15, 1).End(xlDown).Row
      If Not Rows(lngRow).Hidden Then Exit For
  Next
Sheets("DeinBlattname").Range("BO4").Value = CStr(lngRow)
End Sub

Dazu musst du noch irgendwo, wo es nicht stört, im Tabellenblatt die Formel =TEILERGEBNIS(3;A:A) hinterlegen (Achtung, die Formel selbst nicht in Spalte A einfügen, sonst gibt es einen Zirkelbezug).
Hintergrund ist, dass nun bei jeder Neuberechnung vom Teilergebnis auch der Code ausgeführt wird. Und die Neuberechnung erfolgt jedesmal, wenn du die Tabelle filterst bzw. den Filter aufhebst.

[Calafati]

Hallo Berni,

das scheint zu funktionieren. Der Wert stimmt jetzt, sobald der Filter ausgestellt wird (dann 15 = ohnehin oberste Zeile) oder erste Zeilennummer des Filterkriteriums.
Mir ist aufgefallen, daß ich jetzt längere Wartezeiten habe, wenn der Filter wieder ausgestellt wird (die Uhr rödelt). Ist das normal? Wäre aber zu verschmerzen, wenn ja.

(@all) Nochmal meine Frage von vorhin: besteht denn die Möglichkeit, mit dem Filterkriterium selbst zu arbeiten?

Ich habe in einem anderen Tabellenblatt (B) ein paar Lieferantenstammsätze in Form von vereinbarten Lieferkonditionen hinterlegt. Wenn (im Tabellenblatt A) bspw in Feld A5 der Lieferant Müller eingetragen wird (das mache ich bislang noch manuell), holt sich Excel per SVERWEIS auf dieses andere Tabellenblatt den jeweiligen Datensatz und gibt ihn in Feld A6 aus.

Es wäre toll, wenn man dies mit der obigen Filterfunktion verbinden könnte. Dass also der Lieferant, der in der aktuell obersten Zeile steht (und da gefiltert ist, natürlich auch in jeder anderen Zeile) gleichzeitig der Schlüssel für den SVERWEIS ist?!?

Nur wenn das nicht Zuviel Mühe macht.
Besten Dank schonmal und vor allem für die Lösung oben!!!
VG
Klaus

[Calafati]

Habe jetzt auch nochmal die Lösung mit der Formel von Helvetier versucht, bekomme aber in der betreffenden Zelle lediglich die einkopierte Formelangezeigt.

{=INDEX(B:B;MIN(WENN(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(2:100)));ZEILE(2:100))))}
(für den ersten/obersten ausgegebenen Wert)
Ergänzung:
B habe ich durch V ersetzt (meine Spalte)
ZEILE(2:100) durch ZEILE(15:500) (meine maximal zu filternden Spalten)

Die Zelle mit dem Ausgabewert (G5) ist als Standard formatiert

Wahrscheinlich mache ich einfach was falsch, muß mal das Wochendende drüber schlafen :19:
VG
Klaus

[Helvetier]

Hallo Klaus
Den Hinweis im angegebenen Link beachten:
Geschweifte Klammer nicht mit der Tastatur eingeben sondern die Formel =INDEX(B:B;MIN(WENN(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(2:100)));ZEILE(2:100))))
mit Ctrl + Enter abschliessen: Excel macht fast alles für Dich, nur schauen musst selber!

[Calafati]

Das werde ich mir zu Herzen nehmen, versprochen.

Habe die Formel nochmal in die betreffende Zelle eingefügt und wie in meinem vorigen Post entsprechend abgeändert (B=>V, 2:100=>15:500)
Problem nun: ich bekomme, wenn ich die Formel abgeändert habe (und mit CTRL/Enter bestätigt) einen Wert angezeigt, der in Zelle V5 steht (und mit dem eigentlichen Filterkriterium gar nix zu tun hat)?!?

Das sollte doch nicht sein, oder? Oder habe ich dem Fehler doch noch eine Chance gelassen, in dem ich die Zeile nicht richtig modifiziert hab?

Besten Dank und Gruß
Klaus

[Calafati]

Ergänzung: nur wenn der Filter aus ist, wird brav der oberste Begriff aus Zeile 15 übernommen.
Wenn ich den Filter setze, gibt es #NV, trotzdem, daß die entsprechende Zelle (aus einer Zeilenr. >15) mit einem Inhalt versehen ist.

[Helvetier]

Hallo Klaus
Im angegebenen Link finde ich noch diese Bemerkung:
Die Spalte im Ausdruck
INDIREKT("B"&ZEILE(1:100))
darf keine Leerzellen enthalten.
Ansonsten wäre noch zu ermitteln, welcher Teil der Formel das NV bringt. Aber da kann ich nun nichts tun, da mir die Datei nicht zur Verfügung steht.

[Kuwer]

Geschweifte Klammer nicht mit der Tastatur eingeben sondern die Formel ...
mit Ctrl + Enter abschliessen:

Ctrl + Shift + Enter

Gruß Uwe

[RPP63]

Moin!
Da ja VBA eine Option ist:
Wandel die Tabelle in ein ListObject um und benutze den Einzeiler
Tabelle1.Listobjects(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Cells(1).Row
;)

Gruß Ralf

[Helvetier]

Hallo Uwe
Ja, klar! Danke! Vielleicht hat der Fragesteller bewusst oder unbewusst alles richtig gemacht .... oder auch nicht.