Microsoft Excel

Herbers Excel/VBA-Archiv

Daten zählen mit AutoFilter

Betrifft: Daten zählen mit AutoFilter von: TomTom
Geschrieben am: 08.08.2008 23:34:32

Hi,

hier ist ein Code, der nach bestimmten Kriterien schaut und diese zählt und das Ergebnis in Tebelle2 einträgt:

Dim TB1, TB2
Set TB1 = Sheets("Tabelle1")
Set TB2 = Sheets("Tabelle2")

TB2.Range("M8") = WorksheetFunction.CountIf(TB1.Range("D:D"), "Green")
TB2.Range("M10") = WorksheetFunction.CountIf(TB1.Range("D:D"), "Yellow")
TB2.Range("M12") = WorksheetFunction.CountIf(TB1.Range("D:D"), "Red")
TB2.Range("M14") = WorksheetFunction.CountIf(TB1.Range("D:D"), "Blue")

So weit , so gut. Das dumme dabei, es werden auch daensätze mit berücksichtigt, die vom AutoFilter ausgeblendet sind. Wenn der Autofilter aktiv ist, sollen auch nur die sichtbaren Datensätze gezählt werden. Mein jetziger Code zählt aber alle, egal ob ein oder ausgeblendet.

Kann man das irgend wie umgehen.

danke mal
gruss
Tom

  

Betrifft: AW: Daten zählen mit AutoFilter von: Daniel
Geschrieben am: 08.08.2008 23:58:58

Hi

probier mal

TB2.Range("M8") = WorksheetFunction.CountIf(TB1.Range("D:D").SpecialCells(xlcelltypeVisible), "Green")

Gruß, Daniel


  

Betrifft: Kommt eine Fehlermeldung von: TomTom
Geschrieben am: 09.08.2008 00:12:24

Hi Daniel,

da kommt eine Fehlermeldung.

"Die CountIf-Eigenschaftdes WorksheetFunktion-Objektes kann nicht zugeortnet werden"


  

Betrifft: AW: Kommt eine Fehlermeldung von: Daniel
Geschrieben am: 09.08.2008 12:40:48

Hi

ok, könnte daran liegen, daß CountIF keine unterbrochenen Zellbereiche mag.

da muss man sich dann selber eine Countif-funktion programmiereren, die berücksichtigt, ob ein Wert ausgeblendet ist oder nicht:
einfach noch diese Funktion in dein Modul einfügen und die Funktion "ZählenWennSichtbar" anstelle von "WorksheetFunction.CountIf" verwenden:

Public Function ZählenWennSichtbar(Suchmatrix As Range, Bedingung As Variant) As Long
Dim Zelle As Range
Set Suchmatrix = Intersect(Suchmatrix, Suchmatrix.Parent.UsedRange)
For Each Zelle In Suchmatrix
    If Zelle.EntireRow.Hidden = False Then
        If Zelle.Value = Bedingung Then
            ZählenWennSichtbar = ZählenWennSichtbar + 1
        End If
    End If
Next
End Function



so sieht dann der Code in deinem Hauptmakro aus:

TB2.Range("M8") = ZählenWennSichtbar(TB1.Range("D:D"), "Green")

Gruß, Daniel


  

Betrifft: AW: nachtrag von: Daniel
Geschrieben am: 09.08.2008 13:37:10

diese Funktion kannst du auch direkt in Excel verwenden, ohne ein Makro zu benutzen.
das hätte den Vorteil, daß die Funktion dynamisch reagiert, wenn du den Autofilter veränderst.
(im Prinzip analog zu TEILERGEBNIS, nur halt für ZählenWenn)

Gruß, Daniel


  

Betrifft: AW: Daten zählen mit AutoFilter von: Luschi
Geschrieben am: 09.08.2008 10:19:53

Hallo TomTom,

hier mal ein Lösungsansatz:

Dim TB1, TB2, rg As Range, myArray() As String, _
    n0 As Long, n1 As Long, n2 As Long, n3 As Long, n4 As Long
Set TB1 = Sheets("Tabelle1")
Set TB2 = Sheets("Tabelle2")

Set rg = TB1.Range("D:D").SpecialCells(xlCellTypeVisible)
'die Spaltenadressen in ein String-Array schreiben
myArray = Split(rg.Address, ",", -1, vbTextCompare)
n1 = 0: n2 = 0: n3 = 0: n4 = 0
'jeden nichtgeteilten Bereich extra berechnen
For n0 = 0 To UBound(myArray())
    n1 = n1 + Application.WorksheetFunction.CountIf(TB1.Range(myArray(n0)), "Green")
    n2 = n2 + Application.WorksheetFunction.CountIf(TB1.Range(myArray(n0)), "Yellow")
    n3 = n3 + Application.WorksheetFunction.CountIf(TB1.Range(myArray(n0)), "Red")
    n4 = n4 + Application.WorksheetFunction.CountIf(TB1.Range("D:D"), "Blue")
Next n0
TB2.Range("M8") = n1
TB2.Range("M10") = n2
TB2.Range("M12") = n3
TB2.Range("M14") = n4

Set rg = Nothing
Set TB1 = Nothing
Set TB2 = Nothing

Gruß von Luschi
aus klein-Paris


  

Betrifft: Danke Luschi, funktioniert oT von: TomTom
Geschrieben am: 09.08.2008 12:28:42




  

Betrifft: Danke Luschi, funktioniert oT von: TomTom
Geschrieben am: 09.08.2008 12:29:51




  

Betrifft: Danke Luschi, funktioniert oT von: TomTom
Geschrieben am: 09.08.2008 12:30:20

:-)


  

Betrifft: Ich würde es so lösen von: backowe
Geschrieben am: 09.08.2008 13:10:37

Hi Tom,

wenn Dein Bereich nicht die ganze Spalte betrifft, würde ich ihn, damit die Geschwindigkeit höher wird, eingrenzen.

VBA-Code:
Sub ZaehlenwennInGefilterterListe()

Dim TB1, TB2
Set TB1 = Sheets("Tabelle1")
Set TB2 = Sheets("Tabelle2")

With TB2.Range("M8")
  .FormulaLocal = "=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT(""Tabelle1!C""&ZEILE(2:65536)))*(Tabelle1!D2:D65536=""Green""))"
  .Value = .Value
End With

With TB2.Range("M10")
  .FormulaLocal = "=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT(""Tabelle1!C""&ZEILE(2:65536)))*(Tabelle1!D2:D65536=""Yellow""))"
  .Value = .Value
End With

With TB2.Range("M12")
  .FormulaLocal = "=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT(""Tabelle1!C""&ZEILE(2:65536)))*(Tabelle1!D2:D65536=""Red""))"
  .Value = .Value
End With

With TB2.Range("M14")
  .FormulaLocal = "=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT(""Tabelle1!C""&ZEILE(2:65536)))*(Tabelle1!D2:D65536=""Blue""))"
  .Value = .Value
End With

End Sub

Code eingefügt mit Syntaxhighlighter 4.15


Gruss Jürgen


 

Beiträge aus den Excel-Beispielen zum Thema "Daten zählen mit AutoFilter"