Function geht nicht mehr
19.12.2019 03:41:43
Rolf
habe in meinem Excel eine Funktion die mir farbige Felder zählt, hat auch jetzt so ca 8Jahre oder so funktioniert.
Auf einmal bekomme ich eine Fehlermeldung, #Name!
Ich finde aber keinen Fehler
Formel:
=Color1('1'!$C$13:$AG$13;37;'1'!$C$2:$AG$2)+Color1('2'!$C$12:$AG$12;37;'2'!$C$2:$AG$2) +Color1('3'!$C$11:$AG$11;37;'3'!$C$2:$AG$2)+Color1('4'!$C$11:$AG$11;37;'4'!$C$2:$AG$2) +Color1('5'!$C$11:$AG$11;37;'5'!$C$2:$AG$2)+Color1('6'!$C$11:$AG$11;37;'6'!$C$2:$AG$2) +Color1('7'!$C$11:$AG$11;37;'7'!$C$2:$AG$2)+Color1('8'!$C$13:$AG$13;37;'8'!$C$2:$AG$2) +Color1('9'!$C$13:$AG$13;37;'9'!$C$2:$AG$2)+Color1('10'!$C$13:$AG$13;37;'10'!$C$2:$AG$2) +Color1('11'!$C$13:$AG$13;37;'11'!$C$2:$AG$2)+Color1('12'!$C$13:$AG$13;37;'12'!$C$2:$AG$2)
Modul VBA:
Function CountColor(rng As Range, icolor As Integer)
Dim rngAct As Range
Dim iCount As Integer
Application.Volatile
For Each rngAct In rng.Cells
If rngAct = "" And rngAct.Interior.ColorIndex = icolor Then
iCount = iCount + 1
End If
Next rngAct
CountColor = iCount
End Function
Function Color1(rng As Range, icolor As Integer, rngDatum As Range)
Dim rngAct As Range
Dim iCount As Integer
Application.Volatile
For Each rngAct In rng.Cells
If rngAct = "" And rngAct.Interior.ColorIndex = icolor And rngAct.Offset(rngDatum.Row - _
rngAct.Row, 0).Value
iCount = iCount + 1
End If
Next rngAct
Color1 = iCount
End Function
Function Color2(rng As Range, icolor As Integer, rngDatum As Range)
Dim rngAct As Range
Dim iCount As Integer
Application.Volatile
For Each rngAct In rng.Cells
If rngAct = "" And rngAct.Interior.ColorIndex = icolor And rngAct.Offset(rngDatum.Row - _
rngAct.Row, 0).Value > Date Then
iCount = iCount + 1
End If
Next rngAct
Color2 = iCount
End Function
ich zähle Color1 und habe die selbe Formel auch noch mal für Color2
würde mich freuen wenn mir jemand helfen kann
Danke
Anzeige