La consultazione del forum è libera per tutti.
Per poter porre un quesito è invece necessario essere un utente registrato (clicca qui se non lo sei).
Tutti gli utenti che richiedono un supporto, come da REGOLAMENTO, sono caldamente invitati ad allegare un file di esempio con l'indicazione di quello che si desidera ottenere.
Salve,
debbo calcolare su un foglio ("RES") valori che risiedono su un'altro foglio ("OPEX") (con circa 185.000 righe dati) dello stesso file e ho un problema in VBA con una mia Function che contiene la WorksheetFunction.SumIfs.
Nessun problema quando la funzione è chiamata da una Sub (vedi sotto la ProvaMan). 😉
La funzione che ho scritto è:
Public Function MultiSum(ByRef ValStrRange, ByRef FoglioDati, ByRef F2) As Double
For posiz = 1 To Len(ValStrRange) Step 11
Conto = Mid(ValStrRange, posiz, 10)
MultiSum = MultiSum + WorksheetFunction.SumIfs(Worksheets(FoglioDati).Range("M:M"), _
Worksheets(FoglioDati).Range("A:A"), Conto, _
Worksheets(FoglioDati).Range("AB:AB"), "NO", _
Worksheets(FoglioDati).Range("AA:AA"), F2)
Next posiz
End Function
La Sub di prova che la chiama è:
Sub ProvaMan()
FoglioDati = Worksheets("RES").Range("E3")
(E3 che contiene "OPEX")
ValStrRange = Worksheets("RES").Range("B3")
(B3 che contiene "N210030005,N210050003,N301020175,N301020178,N301020275,N301020278")
F2 = Worksheets("RES").Range("F2")
(F2 che contiene "01")
Totale = MultiSum(ValStrRange, FoglioDati, F2)
MsgBox (Totale)
End Sub
Quando invece la mia funzione è avviata da una cella del foglio "RES" con =MultiSum($B3; $E3; F$2), seppure i parametri Conto e F2 siano validi va sempre in errore (#VALORE!) e proprio al momento della WorksheetFunction.SumIfs, il tutto senza nessun messagio in VBA. 😡
Vorrei tanto riuscire a capire e risolvere il problema.
Grazie mille 🤗
Filippo
Ciao
Ho provato il tuo codice in Foglio intonso ed i risultati sono:
a) se in una cella del Foglio inserisco la formula =MultiSum($B3; $E3; F$2) il risultato, dopo aver inserito i giusti dati nelle celle indicate, è 0 (zero)
b) se lancio la sub, invece, si blocca nel richiamo alla Function con la dicitura "Indice non incluso nell'intervallo".
Ora, visto che le colonne A:A, AA:AA, AB:AB sono (nella mia prova) vuote il risultato 0 (zero) mi sembra corretto (ma fino ad un certo punto).
Secondo me, l'errore quando si lancia la macro, sta nel riferimento al Foglio. Mi spiego: la variabile FoglioDati e valorizzata come "OPEX" - poichè nella function viene trasferito quanto assegnato e cioè Worksheets("RES").Range("E3"). Lo sviluppo della funzione WorksheetFunction.SumIfs(Worksheets(FoglioDati).Range("M:M") il riferimento al Foglio non dovrebbe essere FoglioDati bensì "RES".
Oppure, e va tutto come ti aspetti, se in E3 invece di "OPEX" metti "RES"
Sarebbe meglio, però, che allegassi il tuo file per vedere cosa ci sta nelle colonne che io ho lasciato vuote.
Fai sapere. Ciao,
Mario
Allego un esempio con i dati essenziali con la funzione di somma multipla (MultiSum)
Nella cella F3 del foglio RES, da dove eseguo la funzione MultiSum con i parametri previsti il risultato è sempre: #VALORE!
La funzione di interrompe subito alla riga:
MultiSum = MultiSum + WorksheetFunction.SumIfs(Worksheets(FoglioDati).Range("M:M"), _
Worksheets(FoglioDati).Range("A:A"), Codice, _
Worksheets(FoglioDati).Range("AA:AA"), F2)
Quando invece eseguo la sub Prova che contiene gli stessi argomenti della funzione, per il mese 01 non va in errore, ma restituisce un risultato corretto.
Sotto nel folgio RES ho evidenziato i dati di dettaglio.
Grazie per il contributo
F
HO RISOLTO!!
Ho modificato la funzione in questo modo:
Public Function MultiSum1(ByRef ColSum As Range, ByRef ColPar1 As Range, Codici As Range, ByRef ColMese As Range, RifMese As Range) As Double
For Posiz = 1 To Len(Codici) Step 4
Codice = Mid(Codici, Posiz, 3)
MultiSum1 = MultiSum1 + WorksheetFunction.SumIfs(Worksheets("OPEX").Range(ColSum.Address), _ Worksheets("OPEX").Range(ColPar1.Address), Codice, _ Worksheets("OPEX").Range(ColMese.Address), RifMese)
Next Posiz
End Function
Purtroppo ho dovuto forzare il foglio OPEX negli argomenti della SumIfs, perché non sono riuscito a passare nella funzione vba il nome del foglio dati seppure sintatticamente indicato nella formula della cella.
Grazie comunque
Ciao
F
E alla fine ho trovato anche la sintassi corretta per gestire nella funzione il nomefoglio!
Public Function MultiSum1(ByRef ColSum As Range, ByRef ColPar1 As Range, Codici As Range, ByRef ColMese As Range, RifMese As Range) As Double
For Posiz = 1 To Len(Codici) Step 4
Codice = Mid(Codici, Posiz, 3)
MultiSum1 = MultiSum1 + WorksheetFunction.SumIfs(Worksheets(ColSum.Worksheet.Name).Range(ColSum.Address), _
Worksheets(ColPar1.Worksheet.Name).Range(ColPar1.Address), Codice, _
Worksheets(ColMese.Worksheet.Name).Range(ColMese.Address), RifMese)
Next Posiz
End Function
Ciao
F
Complimenti vivissimi 🍾 👍
Ciao,
Mario
Grazie mille!! 😀 Premesso che ho rinominato un pochino i parametri della funzione, sempre sulla stessa vorrei riuscire a gestire automaticamente ulteriori criteri (10 max) che purtroppo diventano utili col tempo.
Un po' come fa excel con la funzione somma.più.se.
Ho provato a dichiararli nella funzione come Optional e la funzione infatti viene chiamata senza errori.
Il problema è che se i criteri in più li metto nella formula e sono vuoti, essendo le condizioni del SumIFs in AND il risultato è sempre zero appena una sola condizione ha zero. 🙄
C'è un modo (sintassi) per scrivere una formula dove i criteri vuoti non vengano considerati nella formula stessa?
Grazie mille
Filippo
Ciao
Forse è meglio "vedere" il codice che hai utilizzato (o che tenti di utilizzare) per capire se ci può essere una soluzione.
Ciao,
Mario
@marius44
Ciao Mario come va?
Sotto le righe che ho scritto per gestire sino a 10 parametri con una formula tipo somma.più.se.
Mi sembra abbastanza elastica e per ora risolve le mie necessità, ma si limita appunto a 10 paramatri max.
Cerco, se pensi ci sia, una soluzione parametrica dove nella formula della funzione si sommino solo gli argomenti che esistono, ma senza ritornare errore per quelli che non sono indicati nella cella formula. Scrivendo quindi una formula generale con tutti paramentri e non le 9 come alla fine ho fatto io.
In pratica testo se le opzioni ci sono e poi gestisco solo i parametri presenti nella formula su misura.
Public Function Multi10ParSum(ByRef RangeArg0 As Range, ByRef RangeArg1 As Range, Arg1 As Variant, Optional ByRef RangeArg2 As Variant, Optional ByRef Arg2 As Variant, _
Optional ByRef RangeArg3 As Variant, Optional ByRef Arg3 As Variant, Optional ByRef RangeArg4 As Variant, Optional ByRef Arg4 As Variant, _
Optional ByRef RangeArg5 As Variant, Optional ByRef Arg5 As Variant, Optional ByRef RangeArg6 As Variant, Optional ByRef Arg6 As Variant, _
Optional ByRef RangeArg7 As Variant, Optional ByRef Arg7 As Variant, Optional ByRef RangeArg8 As Variant, Optional ByRef Arg8 As Variant, _
Optional ByRef RangeArg9 As Variant, Optional ByRef Arg9 As Variant, Optional ByRef RangeArg10 As Variant, Optional ByRef Arg10 As Variant) As Double
For Posiz = 1 To Len(Arg1) Step 4
MultiCode = Mid(Arg1, Posiz, 3)
If IsError(Arg3) = True Then
Multi10ParSum = Multi10ParSum + WorksheetFunction.SumIfs(Worksheets(RangeArg0.Worksheet.Name).Range(RangeArg0.Address), _
Worksheets(RangeArg1.Worksheet.Name).Range(RangeArg1.Address), MultiCode, _
Worksheets(RangeArg2.Worksheet.Name).Range(RangeArg2.Address), Arg2)
GoTo JUMP:
End If
If IsError(Arg4) = True Then
Multi10ParSum = Multi10ParSum + WorksheetFunction.SumIfs(Worksheets(RangeArg0.Worksheet.Name).Range(RangeArg0.Address), _
Worksheets(RangeArg1.Worksheet.Name).Range(RangeArg1.Address), MultiCode, _
Worksheets(RangeArg2.Worksheet.Name).Range(RangeArg2.Address), Arg2, _
Worksheets(RangeArg3.Worksheet.Name).Range(RangeArg3.Address), Arg3)
GoTo JUMP:
End If
If IsError(Arg5) = True Then
Multi10ParSum = Multi10ParSum + WorksheetFunction.SumIfs(Worksheets(RangeArg0.Worksheet.Name).Range(RangeArg0.Address), _
Worksheets(RangeArg1.Worksheet.Name).Range(RangeArg1.Address), MultiCode, _
Worksheets(RangeArg2.Worksheet.Name).Range(RangeArg2.Address), Arg2, _
Worksheets(RangeArg3.Worksheet.Name).Range(RangeArg3.Address), Arg3, _
Worksheets(RangeArg4.Worksheet.Name).Range(RangeArg4.Address), Arg4)
GoTo JUMP:
End If
If IsError(Arg6) = True Then
Multi10ParSum = Multi10ParSum + WorksheetFunction.SumIfs(Worksheets(RangeArg0.Worksheet.Name).Range(RangeArg0.Address), _
Worksheets(RangeArg1.Worksheet.Name).Range(RangeArg1.Address), MultiCode, _
Worksheets(RangeArg2.Worksheet.Name).Range(RangeArg2.Address), Arg2, _
Worksheets(RangeArg3.Worksheet.Name).Range(RangeArg3.Address), Arg3, _
Worksheets(RangeArg4.Worksheet.Name).Range(RangeArg4.Address), Arg4, _
Worksheets(RangeArg5.Worksheet.Name).Range(RangeArg5.Address), Arg5)
GoTo JUMP:
End If
If IsError(Arg7) = True Then
Multi10ParSum = Multi10ParSum + WorksheetFunction.SumIfs(Worksheets(RangeArg0.Worksheet.Name).Range(RangeArg0.Address), _
Worksheets(RangeArg1.Worksheet.Name).Range(RangeArg1.Address), MultiCode, _
Worksheets(RangeArg2.Worksheet.Name).Range(RangeArg2.Address), Arg2, _
Worksheets(RangeArg3.Worksheet.Name).Range(RangeArg3.Address), Arg3, _
Worksheets(RangeArg4.Worksheet.Name).Range(RangeArg4.Address), Arg4, _
Worksheets(RangeArg5.Worksheet.Name).Range(RangeArg5.Address), Arg5, _
Worksheets(RangeArg6.Worksheet.Name).Range(RangeArg6.Address), Arg6)
GoTo JUMP:
End If
If IsError(Arg8) = True Then
Multi10ParSum = Multi10ParSum + WorksheetFunction.SumIfs(Worksheets(RangeArg0.Worksheet.Name).Range(RangeArg0.Address), _
Worksheets(RangeArg1.Worksheet.Name).Range(RangeArg1.Address), MultiCode, _
Worksheets(RangeArg2.Worksheet.Name).Range(RangeArg2.Address), Arg2, _
Worksheets(RangeArg3.Worksheet.Name).Range(RangeArg3.Address), Arg3, _
Worksheets(RangeArg4.Worksheet.Name).Range(RangeArg4.Address), Arg4, _
Worksheets(RangeArg5.Worksheet.Name).Range(RangeArg5.Address), Arg5, _
Worksheets(RangeArg6.Worksheet.Name).Range(RangeArg6.Address), Arg6, _
Worksheets(RangeArg7.Worksheet.Name).Range(RangeArg7.Address), Arg7)
GoTo JUMP:
End If
If IsError(Arg9) = True Then
Multi10ParSum = Multi10ParSum + WorksheetFunction.SumIfs(Worksheets(RangeArg0.Worksheet.Name).Range(RangeArg0.Address), _
Worksheets(RangeArg1.Worksheet.Name).Range(RangeArg1.Address), MultiCode, _
Worksheets(RangeArg2.Worksheet.Name).Range(RangeArg2.Address), Arg2, _
Worksheets(RangeArg3.Worksheet.Name).Range(RangeArg3.Address), Arg3, _
Worksheets(RangeArg4.Worksheet.Name).Range(RangeArg4.Address), Arg4, _
Worksheets(RangeArg5.Worksheet.Name).Range(RangeArg5.Address), Arg5, _
Worksheets(RangeArg6.Worksheet.Name).Range(RangeArg6.Address), Arg6, _
Worksheets(RangeArg7.Worksheet.Name).Range(RangeArg7.Address), Arg7, _
Worksheets(RangeArg8.Worksheet.Name).Range(RangeArg8.Address), Arg8)
GoTo JUMP:
End If
If IsError(Arg10) = True Then
Multi10ParSum = Multi10ParSum + WorksheetFunction.SumIfs(Worksheets(RangeArg0.Worksheet.Name).Range(RangeArg0.Address), _
Worksheets(RangeArg1.Worksheet.Name).Range(RangeArg1.Address), MultiCode, _
Worksheets(RangeArg2.Worksheet.Name).Range(RangeArg2.Address), Arg2, _
Worksheets(RangeArg3.Worksheet.Name).Range(RangeArg3.Address), Arg3, _
Worksheets(RangeArg4.Worksheet.Name).Range(RangeArg4.Address), Arg4, _
Worksheets(RangeArg5.Worksheet.Name).Range(RangeArg5.Address), Arg5, _
Worksheets(RangeArg6.Worksheet.Name).Range(RangeArg6.Address), Arg6, _
Worksheets(RangeArg7.Worksheet.Name).Range(RangeArg7.Address), Arg7, _
Worksheets(RangeArg8.Worksheet.Name).Range(RangeArg8.Address), Arg8, _
Worksheets(RangeArg9.Worksheet.Name).Range(RangeArg9.Address), Arg9)
GoTo JUMP:
End If
If IsError(Arg10) = False Then
Multi10ParSum = Multi10ParSum + WorksheetFunction.SumIfs(Worksheets(RangeArg0.Worksheet.Name).Range(RangeArg0.Address), _
Worksheets(RangeArg1.Worksheet.Name).Range(RangeArg1.Address), MultiCode, _
Worksheets(RangeArg2.Worksheet.Name).Range(RangeArg2.Address), Arg2, _
Worksheets(RangeArg3.Worksheet.Name).Range(RangeArg3.Address), Arg3, _
Worksheets(RangeArg4.Worksheet.Name).Range(RangeArg4.Address), Arg4, _
Worksheets(RangeArg5.Worksheet.Name).Range(RangeArg5.Address), Arg5, _
Worksheets(RangeArg6.Worksheet.Name).Range(RangeArg6.Address), Arg6, _
Worksheets(RangeArg7.Worksheet.Name).Range(RangeArg7.Address), Arg7, _
Worksheets(RangeArg8.Worksheet.Name).Range(RangeArg8.Address), Arg8, _
Worksheets(RangeArg9.Worksheet.Name).Range(RangeArg9.Address), Arg9, _
Worksheets(RangeArg10.Worksheet.Name).Range(RangeArg10.Address), Arg10)
End If
JUMP:
Next Posiz
End Function
Grazie mille
A presto
F
