Trasformare questa formula in VBA

Question

Su un foglio excel, dalla cella B3:B49  alla cella J3:J49 ho la seguente formula:

=SE(L3>=$T$1;MEDIA.PIÙ.SE('Copia foglio1'!$E:$E;'Copia foglio1'!$K:$K;B$2;'Copia foglio1'!$L:$L;$A3);0)

Vorrei trasformare questa formula in VBA, aggiungendo la funzione di leggere 'Copia foglio1' solo nelle celle visibili, cioè quelle che avrò precedentemente filtrato.

Come si fa?

Grazie a chi risponde

Answer 1

Ciao P.PaoloCensori,

visto come si presenta il foglio ho pensato che la tua richiesta di una versione Visual Basic della formula oggetto di questo post derivi dalla necessità di valutarla solo per i record filtrati quindi se questa mia ipotesi è esatta potrebbe andarti bene una soluzione che permetta tale valutazione. Un modo è il seguente:

1. Creare il nome definito "HCella", che sta per "altezza cella", o altro nome di tuo gusto, così:

Formule

Gestione nomi

[ Nuovo... ]

Nome: HCella

Ambito: Cartella di lavoro

Riferito a: =INFO.CELLA(17;INDIRETTO("RC";0))

[ OK ]

[ Chiudi ] 2. Nella tabella dati del foglio "Copia foglio1" aggiungere il campo/colonna:

HRighe

e immettere nella cella "O2" la formula

=HCella

da copiare in basso per quante sono le righe/record della tabella dati. 3. Modificare la formula:

=SE(L3>=$T$1;MEDIA.PIÙ.SE('Copia foglio1'!$E:$E;'Copia foglio1'!$K:$K;B$2;'Copia foglio1'!$L:$L;$A3);0)

in:

=SE(L3>=$T$1;MEDIA.PIÙ.SE('Copia foglio1'!$E:$E;'Copia foglio1'!$K:$K;B$2;'Copia foglio1'!$L:$L;$A3;'Copia foglio1'!$O:$O;">0");0)

Answer 2

Ciao P.PaoloCensori,

non è così immediato costruire la funzione che ipotizzi. Se, e solo se, come argomenti usi intervalli, come nell'esempio, allora forse questo abbozzo (non testato!) potrebbe somigliare da lontano a ciò che chiedi. Col tuo contributo propositivo potremmo anche migliorarlo.

'AVERAGEIFS(average_range _

           ,criteria_range1,criteria1 _

           ,criteria_range2,criteria2…)

Function myAVERAGEIFS(ByVal average_range As Excel.Range _

                    , ByVal only_visible As Boolean _

                    , ParamArray criteria() As Variant)

On Error GoTo ErrH

Dim rng         As Excel.Range

Dim rngAvg      As Excel.Range

Dim rngCritRng  As Excel.Range

Dim rngCrit     As Excel.Range

Dim lngCount    As Long

Dim lngSum      As Long

Dim i           As Long

Dim b           As Boolean

    Set rngAvg = average_range

    With rngAvg

      If .Rows.Count = .EntireColumn.Rows.Count _

        Then Set rngAvg = .Resize(.Cells(.Rows.Count).End(xlUp).Row)

    End With

    Debug.Print rngAvg.Address

    For Each rng In rngAvg

      Debug.Print rng.Address

      If only_visible And rng.EntireRow.Hidden Then

        ' Skip

      Else

        b = True

        For i = LBound(criteria) To UBound(criteria) Step 2

          If TypeName(criteria(i)) = "Range" Then

            Set rngCritRng = criteria(i)

            Set rngCritRng = rngCritRng(rng.Row, 1)

            Debug.Print rngCritRng.Address

          End If

          If TypeName(criteria(i + 1)) = "Range" Then

            Set rngCrit = criteria(i + 1)

            Set rngCrit = rngCrit(rng.Row, 1)

            Debug.Print rngCrit.Address

          End If

          If b Then b = rngCritRng.Value = rngCrit.Value

          If Not b Then Exit For

        Next

        If b Then

          lngCount = lngCount + 1

          lngSum = lngSum + rng.Value

        End If

      End If

    Next

    myAVERAGEIFS = lngSum / lngCount

ExtP:

    Set rngCrit = Nothing

    Set rngCritRng = Nothing

    Set rng = Nothing

    Set rngAvg = Nothing

    Exit Function

ErrH:

    myAVERAGEIFS = CVErr(xlErrValue)

    Resume ExtP

End Function

Da usare, per esempio, così:

• =myAVERAGEIFS(Foglio2!D:D;VERO;Foglio2!G:G;Foglio2!H1;Foglio2!I:I;Foglio2!J1)

Answer 3

Ciao P.PaoloCensori,

la corrispondenza con la tua formula? Non c'è. Era un esempio.

Prova così:

=SE(L3>=$T$1;myAVERAGEIFS('Copia foglio1'!$E:$E;VERO;'Copia foglio1'!$K:$K;B$2;'Copia foglio1'!$L:$L;$A3);0)

Answer 4

Grazie del lavoro, Maurizio.

Io sono un programmatore, ma con altri linguaggi e su altri sistemi, sto avvicinandomi ora a VBA

ed ho molte lacune.

Per esempio, mi pare di capire che quello che mi hai mandato è un programma, e che per eseguirlo

devo copiare nelle celle la "formula" =myAVERAGEIFS(Foglio2!D:D;VERO;Foglio2!G:G;Foglio2!H1;Foglio2!I:I;Foglio2!J1)

ma quale è la corrispondenza con la mia formula?

=SE(L3>=$T$1;MEDIA.PIÙ.SE('Copia foglio1'!$E:$E;'Copia foglio1'!$K:$K;B$2;'Copia foglio1'!$L:$L;$A3);0)

Vorrei profittare della tua competenza per un'altra domandina:

una macro apparentemente semplice, ma non ci riesco.

Devo copiare la colonna E sulla colonna F, che sono filtrate, quindi copiare le celle visibili di E sulle celle visibili di F.

Non posso stabilire un range fisso, perchè il numero di celle può variare in funzione dei filtri.

Come fare?

Ciao!

Paolo

Answer 5

Ciao P.PaoloCensori,

grazie di aver postato la tua domanda nella Microsoft Community.

In relazione alla tua domanda, per una migliore assistenza, ti invito a postare la tua richiesta anche nel forum specifico:

Microsoft Visual Basic Forum

Saluti,

Inga