Supportnet / Forum / Tabellenkalkulation
Zählenwenn in VBA
Frage
Hallo,
ich möchte in meine Dienstplanung für 12 Personen, die Funktion "Zählenwenn" als Makro ausführen.
Zur zeit mache ich es mit folgender Formel in einer Zelle.
=ZÄHLENWENN(C14:AG14;"")+ZÄHLENWENN(C14:AG14;"frei")+ZÄHLENWENN(C14:AG14;"frei!!!")+ZÄHLENWENN(C14:AG14;"Y")+ZÄHLENWENN(C14:AG14;"X")+ZÄHLENWENN(C14:AG14;"AZA")+ZÄHLENWENN(C14:AG14;"NV")
ich habe auch schon folgendes Makro gefunden:
Sub Zusammenzählen()
Anzahl = 0
For i = 6 To 23
If Range("C" & i).Value = "Wahr" Then
Anzahl = Anzahl + 1
End If
Next i
Range("D1").Value = Anzahl
End Sub
.....nur weiß ich nicht wie ich die Abfrage für den Bereich einer Zeile C14 bis AG14 erstelle!
Danke schon mal im voraus
Mario
Antwort 1 von inspiron
.....ich habe mal das probiert, aber es funktioniert noch nicht richtig
Sub Bereich()
Anzahl = 0
If Range("C14:AG14").Select = "U" Then
Anzahl = Anzahl + 1
End If
Range("AM14").Value = Anzahl
End Sub
Sub Bereich()
Anzahl = 0
If Range("C14:AG14").Select = "U" Then
Anzahl = Anzahl + 1
End If
Range("AM14").Value = Anzahl
End Sub
Antwort 2 von Beverly
Hi Mario,
versuche es mal hiermit
Bis später,
Karin
versuche es mal hiermit
Sub zählen()
Range("D1") = Application.WorksheetFunction.CountIf(Range("A6:AG23"), "U")
End Sub
Bis später,
Karin
Antwort 3 von inspiron
Hallo Karin,
danke, funktioniert! Aber D1 sollte automatisch zählen, jetzt wird nur gezählt wenn das makro neu gestartet wird. Was muß ich da machen?
Danke
danke, funktioniert! Aber D1 sollte automatisch zählen, jetzt wird nur gezählt wenn das makro neu gestartet wird. Was muß ich da machen?
Danke
Antwort 4 von fedjo
Hallo Mario,
in "Die Arbeitsmappe" einfügen:
Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Range("D1") = Application.WorksheetFunction.CountIf(Range("A6:AG23"), "U")
End Sub
Gruß
fedjo
in "Die Arbeitsmappe" einfügen:
Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Range("D1") = Application.WorksheetFunction.CountIf(Range("A6:AG23"), "U")
End Sub
Gruß
fedjo
Antwort 5 von Beverly
Hi Mario,
erweitere den Code noch um eine Zeile
und jedes Mal wenn du die Zelle D1 auswählst, wird der Wert berechnet. Andernfalls wird er beim Auswählen jeder Zelle ausgeführt, was nicht erforderlich ist .
Bis später,
Karin
erweitere den Code noch um eine Zeile
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$D$1" Then Exit Sub
Range("D1") = Application.WorksheetFunction.CountIf(Range("A6:AG23"), "U")
End Sub
und jedes Mal wenn du die Zelle D1 auswählst, wird der Wert berechnet. Andernfalls wird er beim Auswählen jeder Zelle ausgeführt, was nicht erforderlich ist .
Bis später,
Karin
Antwort 6 von inspiron
Daaaaaanke!!!!
Antwort 7 von inspiron
Hi, da bin ich wieder!
Wollte den Vorschlag von "fedjo" erweitern
...es funktioniert nicht!
Es kommt eine Fehlermeldung:
"Variable nicht definiert!
Der obere Code funktioniert, nur nicht Automatisch!
Sub F_UZählen()
Frei = WorksheetFunction.CountIf(Range("A14:AG14"), "Frei")
Urlaub = WorksheetFunction.CountIf(Range("A14:AG14"), "U")
Range("D2") = Frei + Urlaub
End Sub
Wollte den Vorschlag von "fedjo" erweitern
Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Frei_a = Application.WorksheetFunction.CountIf(Range("A14:AG14"), "")
Frei_b = Application.WorksheetFunction.CountIf(Range("A14:AG14"), "Frei")
Frei_c = Application.WorksheetFunction.CountIf(Range("A14:AG14"), "Frei!!!")
NV = Application.WorksheetFunction.CountIf(Range("A14:AG14"), "NV")
AZA = Application.WorksheetFunction.CountIf(Range("A14:AG14"), "AZA")
Range("AM14") = Frei_a + Frei_b + Frei_c + NV + AZA
End Sub
...es funktioniert nicht!
Es kommt eine Fehlermeldung:
"Variable nicht definiert!
Der obere Code funktioniert, nur nicht Automatisch!
Antwort 8 von Beverly
Hi Mario,
Option Explicit erfordert zwingend eine Deklaration jeder einzelnen Variablen die man verwendet. Schreibe also an den Anfang deines Codes
Im gegebenen Fall kannst du auch ohne Variablen arbeiten, denn du verwendest sie ja nur ein einziges Mal. Variable verwendet man, wenn man sie mehrfach benutzen oder auch an andere Prozeduren übergeben will. Shreibe einfach
Bis später,
Karin
Option Explicit erfordert zwingend eine Deklaration jeder einzelnen Variablen die man verwendet. Schreibe also an den Anfang deines Codes
Dim Frei_a As Long, Frei_b as Long, Frei_c As Long, NV As Long, AZV As Long
Im gegebenen Fall kannst du auch ohne Variablen arbeiten, denn du verwendest sie ja nur ein einziges Mal. Variable verwendet man, wenn man sie mehrfach benutzen oder auch an andere Prozeduren übergeben will. Shreibe einfach
Range("AM14") = Application.WorksheetFunction.CountIf(Range("A14:AG14"), "") + Application.WorksheetFunction.CountIf(Range("A14:AG14"), "Frei") + usw.
Bis später,
Karin
Antwort 9 von Inspiron8
Hi, es funktioniert, danke!
1. Ich habe es so geschrieben, ist das so o.k. oder gibt es eine einfachere Schreibweise?
2. Ich möchte noch eine Abfrage nur weiß nicht wie, wenn in Zelle B15 ein Name steht, dann
ist B15 leer, dann in AJ15 keine Berechnung!
Wie könnte das gemacht werden?
Danke Mario
1. Ich habe es so geschrieben, ist das so o.k. oder gibt es eine einfachere Schreibweise?
2. Ich möchte noch eine Abfrage nur weiß nicht wie, wenn in Zelle B15 ein Name steht, dann
Range("AJ15") = Frei_a + Frei_b + Frei_c + NV + AZA
ist B15 leer, dann in AJ15 keine Berechnung!
Wie könnte das gemacht werden?
Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim Frei_a As Long
Dim Frei_b As Long
Dim Frei_c As Long
Dim NV As Long
Dim AZA As Long
´Mitarbeiter 1
Frei_a = Application.WorksheetFunction.CountIf(Range("D5:AH5"), "")
Frei_b = Application.WorksheetFunction.CountIf(Range("D5:AH5"), "Frei")
Frei_c = Application.WorksheetFunction.CountIf(Range("D5:AH5"), "Frei!!!")
NV = Application.WorksheetFunction.CountIf(Range("D5:AH5"), "NV")
AZA = Application.WorksheetFunction.CountIf(Range("D5:AH5"), "AZA")
Range("AJ5") = Frei_a + Frei_b + Frei_c + NV + AZA
´Mitarbeiter 2
Frei_a = Application.WorksheetFunction.CountIf(Range("D6:AH6"), "")
Frei_b = Application.WorksheetFunction.CountIf(Range("D6:AH6"), "Frei")
Frei_c = Application.WorksheetFunction.CountIf(Range("D6:AH6"), "Frei!!!")
NV = Application.WorksheetFunction.CountIf(Range("D6:AH6"), "NV")
AZA = Application.WorksheetFunction.CountIf(Range("D6:AH6"), "AZA")
Range("AJ6") = Frei_a + Frei_b + Frei_c + NV + AZA
.
.
.
.
´Mitarbeiter 15
Frei_a = Application.WorksheetFunction.CountIf(Range("D8:AH8"), "")
Frei_b = Application.WorksheetFunction.CountIf(Range("D8:AH8"), "Frei")
Frei_c = Application.WorksheetFunction.CountIf(Range("D8:AH8"), "Frei!!!")
NV = Application.WorksheetFunction.CountIf(Range("D8:AH8"), "NV")
AZA = Application.WorksheetFunction.CountIf(Range("D8:AH8"), "AZA")
Range("AJ8") = Frei_a + Frei_b + Frei_c + NV + AZA
End Sub
Danke Mario
Antwort 10 von Beverly
Hi Mario,
Hinweis: dein Code Workbook_SheetSelectionChange bewirkt, dass diese Summierung in jeder Tabelle ausgeführt wird. Ich nehme mal an, das war auch dein Anliegen.
Bis später,
Karin
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Range("B" & Target.Row) <> "" Then
Range("AJ" & Target.Row) = Application.WorksheetFunction.CountIf(Range("D" & Target.Row & ":AH" & Target.Row), "") + _
Application.WorksheetFunction.CountIf(Range("D" & Target.Row & ":AH" & Target.Row), "Frei") + _
Application.WorksheetFunction.CountIf(Range("D" & Target.Row & ":AH" & Target.Row), "Frei!!!") + _
Application.WorksheetFunction.CountIf(Range("D" & Target.Row & ":AH" & Target.Row), "NV") + _
Application.WorksheetFunction.CountIf(Range("D" & Target.Row & ":AH" & Target.Row), "AZA")
End If
End Sub
Hinweis: dein Code Workbook_SheetSelectionChange bewirkt, dass diese Summierung in jeder Tabelle ausgeführt wird. Ich nehme mal an, das war auch dein Anliegen.
Bis später,
Karin
Antwort 11 von inspiron
Hi ich werde es mal testen, aber wie kann ich in Punkt 2 vorgehen oder habe ich da etwas übersehen?
Mario
Mario
Antwort 12 von inspiron
....ahhhh, ich habe es gerade gesehen!
Ich werd´s mal einbauen.
Danke ich hoffe ich nerve nicht. Ich habe da bestimmt noch mehr fragen!
Ich werd´s mal einbauen.
Danke ich hoffe ich nerve nicht. Ich habe da bestimmt noch mehr fragen!
Antwort 13 von inspiron
....mhh, jetzt ist das Problem, wenn ich einen Namen entferne bleibt der Wert in der Zelle "AJ" stehen.
Antwort 14 von Beverly
Hi Mario,
füge diesen Code hinzu
Bis später,
Karin
füge diesen Code hinzu
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim strBereich As String
Dim loZeile1 As Long, lozeile2 As Long, loZeile As Long
If Target.Count > 1 Then
strBereich = Selection.Address
strBereich = Right(strBereich, Len(strBereich) - InStr(strBereich, "$"))
strBereich = Right(strBereich, Len(strBereich) - InStr(strBereich, "$"))
loZeile1 = Val(Left(strBereich, InStr(strBereich, ":") - 1))
lozeile2 = Val(Right(strBereich, Len(strBereich) - InStrRev(strBereich, "$")))
For loZeile = loZeile1 To lozeile2
Application.EnableEvents = False
ActiveSheet.Cells(loZeile, 36) = ""
Application.EnableEvents = True
Next loZeile
End If
If Intersect(Target, Columns("B")) Is Nothing Then Exit Sub
If Range("B" & Target.Row) = "" Then Range("AJ" & Target.Row) = ""
End Sub
Bis später,
Karin
Antwort 15 von Beverly
Hi Mario,
ich habe gerade festgestellt, das noch ein kleiner Fehler im Code ist
verschiebe bitt folgende Zeile
von der drittletzten Position an die 4. Position (direkt nach der Diemsnsionierung).
Bis später,
Karin
ich habe gerade festgestellt, das noch ein kleiner Fehler im Code ist
verschiebe bitt folgende Zeile
If Intersect(Target, Columns("B")) Is Nothing Then Exit Sub
von der drittletzten Position an die 4. Position (direkt nach der Diemsnsionierung).
Bis später,
Karin
Antwort 16 von inspiron
Danke, es funktioniert.!
schönes WE
schönes WE