Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
884to888
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
884to888
884to888
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Zählenwenn über VBA. Range-Problem

Zählenwenn über VBA. Range-Problem
15.07.2007 04:59:01
Peter
Hallo zusammen.
Ich habe folgendes Problem. Ich möchte gerne über Zählenwenn herausfinden, wieviele Einträge bestimmter Art in verschiedenen Zellen stehen. Dafür habe ich folgende Function :
Range("BM202").Value = WorksheetFunction.CountIf(Range("BM60,BM75,BM90,BM105,BM120,BM135,BM150,BM165"), "M2")
Das Problem an der Sache ist das RangeObjekt. Diese Schreibweise der mehreren einzelnen Zellen wird bei CountIf nicht akzeptiert. Wenn ich z.B.: Range("BM60:BM165") nehme, dann klappt es. Ich habe aber leider nicht die Möglichkeit, die Tabelle dahingehend zu ändern, dass die Bereiche zusammenhängend sind.
Vielleicht wäre auch was über das Cells-Objekt möglich, vielleicht auch , weil die Abstände immer gleich sind mit Offset ? Dafür reichen meine Kenntnisse aber nicht aus. Habt Ihr eine Ahnung, wie ich das am besten anstelle ?
Gruß Peter

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

Betreff
Datum
Anwender
Anzeige
AW: Zählenwenn über VBA. Range-Problem
15.07.2007 07:53:05
Oberschlumpf
Hi Peter
Versuch es in der Zelle BM202 zuerst mal mit dieser Formel:
=SUMME(ZÄHLENWENN(INDIREKT({"BM60"."BM75"."BM90"."BM105"."BM120"."BM135"."BM150"."BM165"});M2))
(gefunden in https://www.herber.de/forum/archiv/488to492/t490863.htm)
Wie du diese Formel in VBA umwandeln kannst, weiß ich aber leider nicht.
Konnte ich trotzdem helfen?
Ciao
Thorsten

AW: Zählenwenn über VBA. Range-Problem
15.07.2007 11:07:00
ingUR
Hallo, Peter,
meiner Kenntnis nach, kann der Zählbefehl, wie auch einige andere Befehle, die mit Zellenbereichen arbeiten, nur einen Bereich untersuchen und der muß ununterbrochen sein.
Nun liegt es an Deiner Anwendung, ob Du nun eine Zellenformel in der Zelle BM202 benötigst oder das Ergebnis der Zählung genügt.
Die Zellenformel, die @Oberschlumpf" herausgesucht hat, läßt sich per VBA als Zellenformel so eintragen:
[BM202].Formula = "=SUM(COUNTIF(INDIRECT({""BM60"",""BM75"",""BM90"",""BM105"",""BM120"","" _ BM135"",""BM150"",""BM165""}),""M2""))"


Jedoch kann sie so nicht in einen Aufruf via WorksheetFunction umgeformt werden, da die Bedeutung der geschweiften Klammern nicht erkannt wird.
Nun kenne ich nicht den Kontext, in dem Deine Programmzeile steht, so dass es verschiedene Möglickeiten gibt, die Aufgabe in VBA umzusetzen.
Vielleicht reicht diese Funktion, die auch als Zellenformel für Dein spezielles Beispiel in Zelle BM202 eingesetzt werden kann ( =CountInRangeStep), zur Erledigung Deiner speziellen Aufgabe:


Function CountInRangeStep() As Long
Dim r As Long, sItem As Variant, c As Long
sItem = [M2]  Zelle mit Suchwert oder Ändernng, wenn String "M2" als Suchzeichenkette
For r = 60 To 165 Step 15
If Cells(r, "B") = sItem Then c = c + 1
Next
CountInRangeStep = c
End Function

In Deinem Programm verkürztt sich die rechte Seite Deiner Zuweisungszeile, und die Zeile sieht so aus:
Range("BM202").Value = CountInRangeStep
Ansonsten bestünde die allgemeine Möglichkeit über UNION, mehrere Bereich zusammenzufassen. Dieser Vereinigungsbereich kann jedoch auch nicht in de Count-Formel benutzt werden:


Sub ZaehlelInMultiRange() as Long
Dim rngMR As Range, rngC As Range, sItem As Variant, c As Long
sItem = [M2]
Set rngMR = Union([BM60], [BM70], [BM85], [BM90], [BM105], [BM120], [BM135], [BM150], [ _
BM165])
For Each rngC In rngMR
If rngC = sItem Then c = c + 1
Next
Set rngMR = Nothing
[BM202] = c
End Sub

Auch diese Sub-Prozedur läßt sich in eine Funktion umschreiben und entsprechend einsetzen.
Gruß,
Uwe

Anzeige
ParamArray
15.07.2007 11:55:43
ransi
Hallo
Interessantes Problem.
Darum habe ich mich auch mal versucht.
Füge diesen Code mal in ein Modul ein:
' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit

Public Function zählenwenn2(Was_zaehlen, ParamArray zellen() As Variant) As Long
Dim L As Long
Dim dummy
For L = 0 To UBound(zellen())
    dummy = dummy + WorksheetFunction.CountIf(zellen(L), Was_zaehlen)
Next L
zählenwenn2 = dummy
End Function

Der Aufruf in einer Tabelle geht dann (fast) genauso wie bei zählenwenn().
Erst das Zuzählende und dann die einzelnen Bereiche...
Tabelle1

 ABCDEF
15   test 
2test     
3 abctest   
4 test  test 
5  efg   
6      
7      
8      
9   test  
10      
11      
12      

Formeln der Tabelle
ZelleFormel
A1=zählenwenn2(A2;B3:C5;D8:D10;E1:E4)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Habe allerdings noch nicht getestet wieviele Bereiche da abgefragt werden können...
ransi

Anzeige
AW: ParamArray
15.07.2007 12:01:06
Nepumuk
Hi,
da reicht doch ein Einzeiler :-)
Public Sub test()
    Range("BM202").Value = UBound(Filter(Array(Range("BM60").Value, Range("BM75").Value, _
        Range("BM90").Value, Range("BM105").Value, Range("BM120").Value, Range("BM135").Value, _
        Range("BM150").Value, Range("BM165").Value), Range("M2").Value)) + 1
End Sub

Gruß
Nepumuk

Anzeige
Zählenwenn über VBA. Range-Problem
15.07.2007 20:04:56
Peter
Hallo Zusammen.
Vielen Dank erstmal für die zahlreichen Antworten und Lösungsvorschläge.
Ich habe mir alle angesehen und auch ausprobiert. Die Variante von Nepomuk hat funktioniert und diese werde ich auch weiter behandeln in diesem Thema. Die anderen werde ich zu einem späteren Zeitpunkt noch mal genauer Testen und eventuell besser Anpassen, als ich das heute aus zeitlichen Gründen machen konnte.
@nepomuk und alle anderen.
Die Berechnung funktionert sehr gut. Jetzt müsste ich das ganze vielleicht noch ein wenig anpassen.
Ich benötige diese Berechnung für 8 verschiedene Bedingungen ( Jede Bedingung einzeln berechnet und ausgegeben). Und diese 8 verschiedenen Werte für jeden Tag eines Jahres, also in 365 Spalten. Jetzt könnte man natürlich dementsprechend das ganze in die Länge ziehen und würde ein Berechnungsmodul von etwa 3km erzeugen :-). Dafür wäre jetzt vielleicht eine Schleife nicht schlecht. Ich habe dazu versucht, die Range-Objekte in Cells zu ändern, das klappt aber nicht. Also werde ich daran noch was rumbasteln.
Das soll Euch aber nicht davon abhalten, gleichzeitig daran zu arbeiten :-).
Hat also jemand eine Idee ?
Gruß Peter

Anzeige
Hinweis:
15.07.2007 21:38:00
Daniel
Hi
just for Information:
in Excel 2003 sind maximal 256 Spalten möglich (ändert sich erst mit Ecesl 2007)
wenn du für jeden Tag eine eigene Spalte brauchst, musst du dir sowieso was anderes einfallen lassen.
Gruß, Daniel

AW: Hinweis:
15.07.2007 21:44:00
Peter
Hallo Daniel.
Ich benutze zwar 2003, habe aber das Jahr auf 2 Tabellen aufgeteilt.
Gruß Peter

AW: Hinweis:
15.07.2007 23:21:29
Gerd
Hallo Peter,

Public Sub test2()
Cells(202, 65).Value = UBound(Filter(Array(Cells(60, 65).Value, Cells(76, 65).Value, _
Cells(90, 65).Value, Cells(105, 65).Value, Cells(120, 65).Value, Cells(135, 65).Value,  _
_
Cells(150, 65).Value, Cells(165, 65).Value), Cells(2, 13).Value)) + 1
End Sub


Dies ist nun wirklich ganz einfach.
Bei "Cells(Zeile,Spalte) kannst natürlich für Zeile bzw. (und) Spalte Variablen einsetzen.
Dim lngZeile as Long, Dim intSpalte as Integer
Cells(lngZeile, intSpalte).Value
u. eine oder ggf. eine Doppel-Schleife drumschreiben.
Die Spaltennummern kriegt, wenn die Bezugsart im Excel Menüleiste-- Extras -- Optionen -- Allgemein
auf Z1S1 umstellst. ( Code in der A1 Schreibweise läuft solange nicht. Bei Verwendung der Cells-Schreibweise ist diese Einstellung egal.
Der Clou war, die angestaubte Filter-Funktion auszugraben :-)
Was Du sonst noch vor hast, ist noch etwas unpräzise rübergekommen.
Gruß Gerd

Anzeige
AW: Hinweis:
16.07.2007 03:25:00
Peter
Hallo nochmal...
Folgendes habe ich jetzt dafür zusammen gebastelt :
Option Explicit

Public Sub test25()
getmoreSpeed (True)
Dim s, M2 As Integer
For M2 = 202 To 209
For s = 9 To 190
Cells(M2, s).Value = UBound(Filter(Array(Cells(60, s).Value, Cells(75, s).Value, _
Cells(90, s).Value, Cells(105, s).Value, Cells(120, s).Value, Cells(135, s).Value, _
Cells(150, s).Value, Cells(165, s).Value), Cells(M2, 1).Value)) + 1
Next s
Next M2
getmoreSpeed (False)
End Sub


Sub getmoreSpeed(bYesNo As Boolean)
Application.ScreenUpdating = Not (bYesNo)
Application.EnableEvents = Not (bYesNo)
Application.Calculation = IIf(bYesNo, xlCalculationManual, xlCalculationAutomatic)
End Sub


getmoreSpeed habe ich im Archiv entdeckt und reduziert die Ablaufzeit von 1:30 min. auf etwa 2 sec.
Findet da irgendjemand noch etwas Verbesserungswürdig ? Oder kann man das so lassen ?
Gruß Peter

Anzeige
AW: Hinweis:
16.07.2007 06:39:24
Nepumuk
Hallo Peter,
noch zwei Bemerkungen.
Dim s, M2 As Integer
1. Damit deklarierst du M2 als Interger und s als Variant, da kein Typ angegeben ist. In weiser Voraussicht auf Excel2007 würde ich die Variablen sowieso als Long deklarieren. Auch weil das Cells - Objekt für Zeile und Spalte einen Long - Wert erwartet und damit der Typ nicht automatisch konvertiert werden muss, was natürlich auch Zeit kostet und die 2 Byte mehr bringen das Fass noch nicht zum überlaufen.
2.
a) Du solltest dir angewöhnen sprechende Variablennamen zu benutzen. Diese können 255 Zeichen lang sein, ohne dass sie mehr Speicher benötigen als ein einzelner Buchstabe. Nutze beim programmieren auch die automatische Wortergänzung, nach der ersten 4 bis 5 Buchstaben einfach auf Strg + Blank drücken !!!
b) Wenn du deine Variablen mit einem Präfix versiehst, wird das Programm sowohl für dich wie auch für andere leichter lesbar. Lies mal hier: http://www.it-academy.cc/content/article_browse.php?ID=995
Gruß
Nepumuk

Anzeige
Frage geklärt
16.07.2007 20:45:47
Peter
Hallo Nepumuk (und die anderen).
Danke nochmal für die Infos. Hab ich mir angesehen und auch schon was angepaßt.
Hätte nicht gedacht, dass es so einfach sein kann, wenn man sich nur ein wenig Gedanken macht :-).
Natürlich muss als Starthilfe schon mal so ein Wink mit dem Zaunpfahl her, aber den nehme ich gerne von Dir/Euch an.
Vielen Dank nochmal
Gruß Peter

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige