Excel UDF (User Defined Function) conta e somma celle per colore (riempimento)

Question

Buon Giorno a tutti

Utilizzo  due  UDF (ricevute dalla Community che ringrazio !! ) per contare e sommare una serie di celle in una tabella  a seconda del colore di riempimento delle stesse

i codici delle UDF (in un Modulo std di VBA) sono questi :

Function ContaCellePerColore(rData As Range, cellRefColor As Range) As Long

     Dim indRefColor As Long

     Dim cellaCorrente As Range

     Dim cntRes As Long

     Application.Volatile

     cntRes = 0

     indRefColor = cellRefColor.Cells(1, 1).Interior.Color

     For Each cellaCorrente In rData

         If indRefColor = cellaCorrente.Interior.Color Then

             cntRes = cntRes + 1

         End If

     Next cellaCorrente

     ContaCellePerColore = cntRes

 End Function

 Function SommaCellePerColore(rData As Range, cellRefColor As Range)

     Dim indRefColor As Long

     Dim cellaCorrente As Range

     Dim sumRes

     Application.Volatile

     sumRes = 0

     indRefColor = cellRefColor.Cells(1, 1).Interior.Color

     For Each cellaCorrente In rData

         If indRefColor = cellaCorrente.Interior.Color Then

             sumRes = WorksheetFunction.Sum(cellaCorrente, sumRes)

         End If

     Next cellaCorrente

     SommaCellePerColore = sumRes

 End Function

la colonna contenente i dati (negativi e positivi ) e' la A  il Range e' da A2 a A266

I colori  utilizzati sono 5 e sono nelle celle C1 ,D1,E1,F1,G1

Nelle celle  C2,D2,E2,F2,G2    e' inserita la Formula   =ContaCellePerColore(A2:A266;$C$1)   (... $D$1 ... etc..)

 """     """    C4,D4,E4,F4,G4     "    """       "    """"        =SommaCellePerColore(A2:A266;$C$1)   (... $D$1 ... etc..)

vorrei utilizzare questo sistema per contare quante celle della colonna A contengono numeri negativi inferiori a -500 ,-1000 , -2000 e altre 2 condizioni e poi sommare tutti i valori delle 5 diverse condizioni (ogni condizione e' associata ad un colore ...)

Se  il riempimento delle celle (colore) e' fatto manualmente le UDF funzionano perfettamente

se invece il riempimento delle celle e' Conseguente  a  Formattazione Condizionale  .... Non Funzionano .. !!??

Perche'  ??  non si puo' fare ...oppure deve essere modificato il codice ??

Grazie  per qualsiasi suggerimento     Claudio P

Answer 1

Ciao Claudio,

Utilizzo  due  UDF (ricevute dalla Community che ringrazio !! ) per contare e sommare una serie di celle in una tabella  a seconda del colore di riempimento delle stesse

i codici delle UDF (in un Modulo std di VBA) sono questi :

Function ContaCellePerColore(rData As Range, cellRefColor As Range) As Long

     Dim indRefColor As Long

     Dim cellaCorrente As Range

     Dim cntRes As Long

     Application.Volatile

     cntRes = 0

     indRefColor = cellRefColor.Cells(1, 1).Interior.Color

     For Each cellaCorrente In rData

         If indRefColor = cellaCorrente.Interior.Color Then

             cntRes = cntRes + 1

         End If

     Next cellaCorrente

     ContaCellePerColore = cntRes

 End Function

 Function SommaCellePerColore(rData As Range, cellRefColor As Range)

     Dim indRefColor As Long

     Dim cellaCorrente As Range

     Dim sumRes

     Application.Volatile

     sumRes = 0

     indRefColor = cellRefColor.Cells(1, 1).Interior.Color

     For Each cellaCorrente In rData

         If indRefColor = cellaCorrente.Interior.Color Then

             sumRes = WorksheetFunction.Sum(cellaCorrente, sumRes)

         End If

     Next cellaCorrente

     SommaCellePerColore = sumRes

 End Function

la colonna contenente i dati (negativi e positivi ) e' la A  il Range e' da A2 a A266

I colori  utilizzati sono 5 e sono nelle celle C1 ,D1,E1,F1,G1

Nelle celle  C2,D2,E2,F2,G2    e' inserita la Formula   =ContaCellePerColore(A2:A266;$C$1)   (... $D$1 ... etc..)

 """     """    C4,D4,E4,F4,G4     "    """       "    """"        =SommaCellePerColore(A2:A266;$C$1)   (... $D$1 ... etc..)

vorrei utilizzare questo sistema per contare quante celle della colonna A contengono numeri negativi inferiori a -500 ,-1000 , -2000 e altre 2 condizioni e poi sommare tutti i valori delle 5 diverse condizioni (ogni condizione e' associata ad un colore ...)

Se  il riempimento delle celle (colore) e' fatto manualmente le UDF funzionano perfettamente

se invece il riempimento delle celle e' Conseguente  a  Formattazione Condizionale  .... Non Funzionano .. !!??

Perche'  ??  non si puo' fare ...oppure deve essere modificato il codice ??

Innanzitutto, anche se si trattasse dei colori non impostati dalla formattazione condizionale, ci potrebbe essere dei problemi con le tue formule.  Più in particolare, la tua formula

        =ContaCellePerColore(A2:A266;**$**C$1) 

avrebbe dovuto essere

        =ContaCellePerColore(**$A$2:$A$266;C$**1)

In modo analogo, la tua formula

        =SommaCellePerColore(A2:A266;$C**$**1)

dovrebbe essere

        =SommaCellePerColore(**$A$2:$A$266;C$**1)

Tuttavia, le tue funzioni non risponderanno, e non possono, rispondere ai colori impostati dalla formattazione condizionale.

La soluzione più semplice sarebbe quella di utilizzare formule che sfruttano la stessa logica di quella utilizzata per la formattazione condizionale. Ad esempio, se lo sfondo delle celle fosse impostato al giallo mediante le condizioni di formattazione condizionale <-1000 e >-2000, il numero di celle gialle verrebbe restituito con la semplice formula:

=MATR.SOMMA.PRODOTTO(--($A$2:$A$266<-1000);--($A$2:$A$266>-2000))

Invece della funzione MATR.SOMMA.PRODOTTO, si potrebbe anche utilizzare la funzione CONTA.PIÙ.SE.

===

Regards,

Norman

Answer 2

Grazie  Norman

....non si finisce mai di imparare

comunque peccato sarebbe stato comodo

                               Buone Feste               Claudio P