Gekleurde cellen wel of niet mee optellen

[sb17]

Beste helpers,

In bijgevoegd bestand een voorbeeld hoe ik de gele celwaarden wel of niet in de optelling wil hebben. De somkleurfunctie heb ik gekopieerd,maar werkt nu niet, en zelf is mijn vba knobbel te klein om hier uit te komen. De gewone optelling is niet het punt, maar het uitsluiten van de gele cellen wil niet lukken, kan iemand helpen?

Siebe

Bekijk bijlage Map1.xlsm

 

[edmoor]

Je wilt dus kunnen aangeven of de gekleurde cellen wel of niet meegeteld moeten worden. Zet dan de volgende functie in een module:

Public Function SOMINCKLEUR(Bereik As Range, CelKleur As Range, Incl As Integer) As Variant
    Dim i As Long
    
    For i = Bereik.Row To Bereik.Rows.Count + Bereik.Row - 1
        Select Case Incl
            Case 0
                SOMINCKLEUR = SOMINCKLEUR + Cells(i, Bereik.Column)
            Case 1
                If Cells(i, CelKleur.Column).Interior.Color <> CelKleur.Interior.Color Then
                    SOMINCKLEUR = SOMINCKLEUR + Cells(i, Bereik.Column)
                End If
            Case 2
                If Cells(i, CelKleur.Column).Interior.Color = CelKleur.Interior.Color Then
                    SOMINCKLEUR = SOMINCKLEUR + Cells(i, Bereik.Column)
                End If
        End Select
    Next i
End Function

Voorbeeld aanroep in B12 met meetellen gekleurde cellen:
=SOMINCKLEUR(B4:B11;B6;0)

Voorbeeld aanroep in B12 met gekleurde cellen NIET meetellen:
=SOMINCKLEUR(B4:B11;B6;1)

Voorbeeld aanroep in B12 met alleen gekleurde cellen tellen:
=SOMINCKLEUR(B4:B11;B6;2)

 

[sb17]

Beste ed moore en andere helpers,

Ik heb de gegeven code geplaatst en een sub toegevoegd om het aanroepen in cel b12 via opdracht knop te kunnen doen, maar het loopt niet goed, weet iemand hoe het wel moet?

SiebeBekijk bijlage Map1 met public function.xlsm

 

[VenA]

Wat loopt er zoal niet goed? De som is 240 en de som - de som van de gele cellen (36) = 204. Lijkt mij te kloppen.

Edit dit lijkt de boosdoener

Cells(12, 2).FormulaR1C1 = "=sominckleur(R[-8]C:R[-1]C,R[-10]C[-1],0)"

Je gaat een verkeerde range optellen.

 

[sb17]

Beste V en A,

Nu idd loopt het bij mij ook goed, heb hem even opgeslagen en weer geopend, zopas bleef hij hangen en kreeg ik bij "nee " geen resultaat, vandaar de vraag.

Maar hoe krijg ik dezelfde optelling nu ook in cel c14 t/m h14? Wat moet ik dan in de code aanpassen zonder alle cellen te hoeven benoemen?

Siebe

 

[HSV]

Test het zo eens.

  Cells(12, 2).Resize(, 7) = "=sominckleur(R[-8]C:R[-1]C,R2C1,0)"

Edit: er van uitgaande in de cellen B12:H12

Voor B14:H14:

Cells(12, 2).Resize(, 7) = "=sominckleur(R[-10]C:R[-3]C,R2C1,0)"

 

[sb17]

Beste HSV,

Dit is idd wat ik bedoelde, dank hiervoor.

Siebe

 

[sb17]

Beste helpers,

In het voorbeeld bestand werkt het goed, maar als ik de code naar een ander bestand kopieer krijg ik in de cel waar de aanroep staat[=sominckleur(Q8:Q245;$A$2;1)] de fout melding #Waarde!.
Wat kan dit zijn?

Siebe

 

[HSV]

Plaats het bestand waarin het fout gaat Siebe.
Hier zijn niet veel helderzienden.

 

[sb17]

Harry,

Uiteraard heb je gelijk, maar is groot bestand met vertrouwelijke info, vandaar eerst zo gevraagd.

Maar nu een uitsnede uit dit bestand gemaakt, en bijgevoegd.

In (P6-DA6) staat volgens mij de juiste formule, maar toch de foutmelding dus.

Siebe

Bekijk bijlage Gele cellen meetellen 2 .xlsm

 

[HSV]

Siebe,

De code is aangepast zodat het werkt (celkleur.column → bereik.column).

Alleen je hebt iets in de zogenaamde lege cellen staan die de foutwaarde veroorzaken.
De formule kan bv geen som maken van 1+chr(10)+20+chr(1)+..........??????????

Ik heb de zogenaamde lege cellen leeg gemaakt met 'delete', maar alleen voor kolom R.

 

[sb17]

Harry,

Idd er staat bijv in P8 [=ALS($H8=CEL("Inhoud";P$6);-1*SOM($L8:$O8)+$J8;"")] de 'lege cel' wordt gestuurd door "" dus, kan ik dit ergens door vervangen? Ws wordt dit als tekst gelezen en kan hierdoor niet worden opgeteld?

Heb dit gedaan om te voorkomen dat er steeds een 0 (null) komt te staan en alles vol staat met nullen. Heb je nog een tip hiervoor?

Siebe

 

[Ginger]

Kleuren optellen... Als je een cel kleurt op basis van een criterium kan je ook een hulpkolom inzetten met een bepaalde waarde. Zo kan je heel simpel gebruik maken van bestaande functies.

 

[HSV]

Misschien heeft Leo een leuk idee, maar even verder geborduurd op de code van @edmoor.

For i = Bereik.Row To Bereik.Rows.Count + Bereik.Row - 1
     If isnumeric(Cells(i, Bereik.Column)) Then

Boven 'Next i zet je een 'end if' bij

 

[WHER]

Nog een verderborduursel:

SOMINCKLEUR = WorksheetFunction.Sum(SOMINCKLEUR, Cells(i, Bereik.Column))

 

[sb17]

Harry e.a.,

Nu telt hij idd goed op, dank je.
Maar waar de telling 0 is staat ook een nul, logisch, maar wel onoverzichtelijk.

Als ik in het werkblad de formule aanpas naar: [ALS(sominckleur(Q8:Q245;$A$2;0)>0;sominckleur(Q8:Q245;$A$2;0);"")], zijn de nullen weg, en omgezet naar vba maak ik er dit van['Worksheets("jan").Cells(7, 16).Resize(, 90) = "=als(sominckleur(R[1]C:R[238]C,R2C1,0)>0;sominckleur(R[1]C:R[238]C,R2C1,0);"")". Maar helaas dat werkt dan weer net niet.

Weet je toevallig wat er fout staat?

Siebe

 

[HSV]

Bv.

"=if(sominckleur(R[1]C:R[238]C,R2C1,0)=0,"""",sominckleur(R[1]C:R[238]C,R2C1,0))"

 

[sb17]

Harry e.a.,

Dank je,dit is idd de juiste formule ( kwam er achter dat ik dat via macro opnemen ook had kunnen vinden, weer wat geleerd zij het mosterd na de maaltijd .

Nu wil ik eigenlijk de macro kunnen aanroepen met de opdrachtknop, dat lukt , maar het werkt alleen juist in de sheet waaruit het wordt aangeroepen, en in de andere niet goed. in de sheets "jan"en "ovl" moeten de cellen wel of niet worden geteld. ( In jan geel wel of niet meetellen, en in ovl alleen geel tellen of niets optellen, zie code in bijlage.)

Als ik de knop knip en plak naar jan werkt het in jan goed en omgekeerd.

Wat moet in de code veranderd worden zodat het vanuit een willekeurige sheet kan worden aangeroepen?

Siebe

Bekijk bijlage Gele cellen meetellen 2 .xlsb

 

[HSV]

Neem de namen van het blad mee in de formule.

"=if(sominckleur([COLOR=#FF0000]jan![/COLOR]R[1]C:R[238]C,R2C1,1)=0,"""",sominckleur([COLOR=#FF0000]jan![/COLOR]R[1]C:R[238]C,R2C1,1))"

 

[sb17]

Code aangepast zoals gezegd, maar probleem blijft, is er iets anders loos?

Siebe

Bekijk bijlage Gele cellen meetellen 3 .xlsb