[RISOLTO] Trasformare una formula matriciale in vba

Question

Buongiorno a tutti, avrei la necessità di trasformare questa formula di matr.somma.prodotto in vba.

=MATR.SOMMA.PRODOTTO(--(TESTO($D$2:$D$43;"mmmm")=H2);$F$2:$F$43)

Ho provato scopiazzando e modificando un codice trovato nel forum ma senza risultato.....

Dim Res As Variant

Res = Evaluate("=SUMPRODUCT(--(text(D1:D1000)=p2)*" _

& "(D1:D1000))")

MsgBox Res

Mi ritorna sempre Errore 13 valore non corrispondente....non sò più che pesci pigliare....

Ringrazio in anticipo

Ringo

Answer 1

Ciao Ringo,

Riconoscendo una deplorevole mancanza di pazienza da parte mia, e non desiderando che la notte passi senza rettificare la situazione, verifica se il seguente codice soddisfa le tue esigenze:

In un modulo standard, incolla:

'=========>>

Option Explicit

'--------->>

Public Sub Tester()

    Dim WB As Workbook

    Dim SH_Festività As Worksheet, SH_Generalità As Worksheet

    Dim rngMesi As Range

    Dim dValGeneralità As Double

    Dim iYear As Long

    Dim i As Long

    Set WB = ThisWorkbook

    With WB

        Set SH_Festività = .Sheets("Festività Svizzere")

        Set SH_Generalità = .Sheets("Generalità")

    End With

    With SH_Festività

        Set rngMesi = .Range("H2:S2")

        iYear = Year(.Range("B2").Value)

    End With

    dValGeneralità = SH_Generalità.Range("D2").Value

    With rngMesi

        For i = 1 To .Cells.Count

            .Cells(i).Value = DateSerial(iYear, i, 1)

            .Cells(2, i).Value = Evaluate( _

                                 "=SUMPRODUCT(--(MONTH($D$2:$D$43)=MONTH(" _

                                 & .Cells(i).Address(0, 0) & ")),$F$2:$F$43)")

            .Cells(3, i).Value = .Cells(2, i).Value * dValGeneralità

        Next i

        .NumberFormat = "mmmm"

        .Offset(2).NumberFormat = """fr."" #,##0.00"

    End With

End Sub

'<<=========

Nota bene:

• Ho cancellato il tuo codice nel modulo di codice del foglioFestività Svizzere in quanto, a mio modesto parere, solo il codice di evento dovrebbe trovarsi in quel modulo.  A questo proposito vedi l'ottimo articolo wiki di Mauro Gamberini:

 Excel - Dove e come inserire il codice Visual Basic - VBA (Update)

• Per provare il mio codice, ho creato un nuovo foglio denominato Generalità e, nella sua cella D3 ho inserito la funzione =PI.GRECO()

Potresti scaricare il mio file di prova Ringo20180619.xlsm

===

Regards,

Norman

Answer 2

Ho modificato come mi hai detto e funziona.

Piccola modifica, ho creato un ciclo per i dodici mesi con questo codice:

ultima = Cells(2, Cells.Columns.Count).End(xlToLeft).Column

For i = 8 To ultima

Cells(3, i) = Evaluate( _

 "=SUMPRODUCT(--(MONTH($D$2:$D$43)=MONTH(H2)),$F$2:$F$43)")

Cells(4, i) = Cells(3, i) * Sheets("Generalità").Range("D2")

Next

Al posto di H2 ho messo Cells(2, i), ma non mi fa il ciclo, esce il valore #NOME?

Qualche idea?

Answer 3

Ciao Ringo,

ciao Norman, ho testato la tua formula vba, sempre lo stesso errere 13

A me la formula inserita nel foglio funzione sia con le mie impostazioni inglesi che le impostazioni italiani. 

Comunque, mentre la funzione VBA Evaluate funziona per me con il testo inglese del mese nella cella H2, non funziona se avessi i corrisipondenti nomi italiani immessi in H2 perchè VBA parla solo inglese.

Quindi, per ovviare questo problema, sostituisci il testo della cella H2 con una vera data, magari applicando una formato personalizzato mmmm, per visualizzare solo il mese,  e prova il seguente codice:

'=========>>

Public Sub Tester()

    Dim Rng As Range

    Dim Res As Variant

    Set Rng = ActiveSheet.Range("A1")

    Rng.Formula = _

    "=SUMPRODUCT(--(TEXT($D$2:$D$43,""mmmm"")=TEXT(H2,""mmmm"")),$F$2:$F$43)"

    Res = Evaluate( _

          "=SUMPRODUCT(--(TEXT($D$2:$D$43,""mmmm"")" _

                              & "=TEXT(H2,""mmmm"")),$F$2:$F$43)")

    Call MsgBox( _

         Prompt:=Res, _

         Buttons:=vbInformation, _

         Title:="REPORT")

End Sub

'<<=========

Tuttavia, sempre con una vera data in H2, credo preferibile e più semplice sia invece il seguente codice che fa un confronto diretto tra i mesi:

'=========>>

Public Sub Tester2()

    Dim Rng As Range

    Dim Res As Variant

    Set Rng = ActiveSheet.Range("A1")

    Rng.Formula = _

    "=SUMPRODUCT(--(MONTH($D$2:$D$43)=MONTH(H2)),$F$2:$F$43)"

    Res = Evaluate( _

          "=SUMPRODUCT(--(MONTH($D$2:$D$43)=MONTH(H2)),$F$2:$F$43)")

    Call MsgBox( _

         Prompt:=Res, _

         Buttons:=vbInformation, _

         Title:="REPORT")

End Sub

'<<=========

Potresti scaricare il mio file di prova Ringo20180618.xlsm

===

Regards,

Norman

Answer 4

ciao Norman, ho testato la tua formula vba, sempre lo stesso errere 13

Inserendo in una cella del foglio, mi da #VALORE!

Answer 5

Ciao Ringo.

Buongiorno a tutti, avrei la necessità di trasformare questa formula di matr.somma.prodotto in vba.

=MATR.SOMMA.PRODOTTO(--(TESTO($D$2:$D$43;"mmmm")=H2);$F$2:$F$43)

Prova:

    ActiveCell.Formula = _

    "=SUMPRODUCT(--(TEXT($D$2:$D$43,""mmmm"")=H2),$F$2:$F$43)"

Ho provato scopiazzando e modificando un codice trovato nel forum ma senza risultato.....

Dim Res As Variant

Res = Evaluate("=SUMPRODUCT(--(text(D1:D1000)=p2)*" _

& "(D1:D1000))")

MsgBox Res

Mi ritorna sempre Errore 13 valore non corrispondente....non sò più che pesci pigliare....

Prova qualcosa del genere:

'=========>>

Option Explicit

'--------->>

Public Sub Tester()

    Dim Res As Variant

  Res = Evaluate( _

        "=SUMPRODUCT((TEXT($D$2:$D$43,""mmmm"")=H2)*$F$2:$F$43)")

  Call MsgBox( _

           Prompt:=Res, _

           Buttons:=vbInformation, _

           Title:="REPORT")

End Sub

'<<=========

===

Regards,

Norman