Somme.si.ens avec somme de 2 colonnes

Je resterai donc avec mon vieux complice Sommeprod, même s'il est soupçonné
de gourmandise. -))

Ouille, par contre dans mon cas, ça serait pour utiliser cette formule en VBA
afin d'en faire une fonction permettant dans les formules de ressortir les
sommes des valeurs correspondants à un compte comptable donné. Par exemple,
j'aurai =Débit(701) qui me cherchera dans toutes les écritures comptables le
débit du 701. En sachant que sur un classeur je risque d'utiliser de
nombreuses fois cette formule... Il me faudrait donc quelque chose de
rapide/pas gourmand.

@+
HD

Bonjour,
A ta place, j'essaierais d'abord avec les formules; 3000 lignes, ce
n'est pas la mer à boire. Maintenant, effectivement, ça dépend aussi du
nombre de formules.
Voici une foction VBA. Le premier paramètre est la chaîne recherchée,
les second, la plage ou se fait la recherche; la seconde plage est la
plage à additionner. Il peut y avooir une seconde plage à additionner.
Les plages doivent avoir la même taille. eg.
=débit("7*";C2:C14;F2:F14)
=débit("7*";C2:C14;F2:F14;L2:L14)

Function Débit(Quoi As String, Cherche As Range, Add As Range, Optional
Add2 As Range) As Double
Dim C As Range
Application.Volatile
If Add2 Is Nothing Then
For i = i To Cherche.Count
If Cherche(i) Like Quoi Then
Débit = Débit + Add(i)
End If
Next i
Else
For i = i To Cherche.Count
If Cherche(i) Like Quoi Then
Débit = Débit + Add(i) + Add2(i)
End If
Next i
End If
End Function

Daniel

J'ai adapté le script :
Function Debit2(Quoi As String) As Double
Dim C As Range
Dim Cherche As Range
Dim Add As Range

Set Cherche = Sheets("Ecritures").Columns(3)
Set Add = Sheets("Ecritures").Columns(6)
Application.Volatile

For i = i To Cherche.Count
If Cherche(i) Like Quoi Then Debit2 = Debit2 + Add(i)
Next i
End Function

Mais lorsque je fais Þbit2(7) dans une cellule j'obtiens un #VALEUR!

@+
HD

Ca fonctionnerait comme ceci, mais tu parcours des colonnes entières,
donc c'est très long :

Function Debit2(Quoi As String) As Double
Dim C As Range
Dim Cherche As Range
Dim Add As Range

Set Cherche = Sheets("Ecritures").Columns(3).Cells
Set Add = Sheets("Ecritures").Columns(6).Cells
Application.Volatile

For i = 1 To Cherche.Count
If Cherche(i) Like Quoi Then Debit2 = Debit2 + Add(i)
Next i
End Function

Daniel

J'ai adapté le script :
Function Debit2(Quoi As String) As Double
Dim C As Range
Dim Cherche As Range
Dim Add As Range

Set Cherche = Sheets("Ecritures").Columns(3)
Set Add = Sheets("Ecritures").Columns(6)
Application.Volatile

For i = i To Cherche.Count
If Cherche(i) Like Quoi Then Debit2 = Debit2 + Add(i)
Next i
End Function

Mais lorsque je fais Þbit2(7) dans une cellule j'obtiens un #VALEUR!

@+
HD

Ca fonctionnerait comme ceci, mais tu parcours des colonnes entières, donc
c'est très long

Même en réduisant la plage cela reste encore très long... en tout cas
beaucoup plus l'on qu'avec la fonction Somme.Si.Ens que l'on utiliserait en
VBA.

Comme je l'ai écris dans mes précédents messages de ce fil de discussion, je
risque d'utiliser de nombreuses fois ma fonction "Débit" sur le classeur
(peut être une centaine de fois voir plus...).

@+
HD

Bonjour MichD,

J'ai voulu utiliser la fonction Evaluate pour utiliser la formule
Somme.Si.Ens en vba mais cela me renvoi 0 là où je devrais avoir un autre
montant.

Avec:
FEcr="Ecritures"
ColCpt=3
ColDebit=6
et Cpt=7

MsgBox Evaluate("SUMIFS(Sheets(" & Fecr & ").Columns(" & ColDebit & "),
Sheets(" & Fecr & ").Columns(" & ColCpt & "), ""="" & cpt & ""*"")")
me renvoi 0...

MsgBox Evaluate("=SUMIFS(Ecritures!C6,Ecritures!C3,""=7*"")")
Me renvoi 0...

alors qu'avec ma formule directement sur la cellule
=SOMME.SI.ENS(Ecritures!$F:$F;Ecritures!$C:$C;"=7*")
j'obtiens 92 763,29.

Même en enregistrant une macro avec double clic sur la formule pour voir ce
que cela donne en VBA j'obtiens une formule qui une fois lancée en VBA me
ressort encore 0.

@+
HD

Ca fonctionnerait comme ceci, mais tu parcours des colonnes entières, donc
c'est très long

Même en réduisant la plage cela reste encore très long... en tout cas
beaucoup plus l'on qu'avec la fonction Somme.Si.Ens que l'on utiliserait en
VBA.

Comme je l'ai écris dans mes précédents messages de ce fil de discussion, je
risque d'utiliser de nombreuses fois ma fonction "Débit" sur le classeur
(peut être une centaine de fois voir plus...).

@+
HD

VBA est plus long que les fonctions Excel, c'est pourquoi je t'ai
recommandé de commencer par utiliser des formules.
Daniel

VBA est plus long que les fonctions Excel, c'est pourquoi je t'ai
recommandé de commencer par utiliser des formules.

Sauf qu'avec Evaluate l'on peut utiliser des formules Excel directement dans
le VBA. Cela m'a déjà par le passé permis de gagner beaucoup de temps par
rapport à du code en "vba pur".

@+
HD

Mais avec Evaluate, tu exécutes la fonction Excel, donc tu nas de gain
que dans le cas où tu n'as qu'une partie de la feuille à recalculer.
Daniel

VBA est plus long que les fonctions Excel, c'est pourquoi je t'ai
recommandé de commencer par utiliser des formules.

Sauf qu'avec Evaluate l'on peut utiliser des formules Excel directement dans
le VBA. Cela m'a déjà par le passé permis de gagner beaucoup de temps par
rapport à du code en "vba pur".

@+
HD

Bonjour,

Voici un exemple avec "Evaluate"
Ce critère Crit = """>7*""" s'applique seulement si
tu fais référence à une chaîne de caractère texte où
que tu aies formaté "Texte" la plage de cellules avant
d'y saisir tes nombres.

Cependant, comme dans l'exemple, tu peux utiliser
une autre formule.

'-----------------------------------------
Sub test()
Dim PlgSomme As String
Dim PlgCrit As String
Dim Crit As String

With Worksheets("Ecritures")
derlig = .Range("C65536").End(xlUp).Row
PlgSomme = .Name & "!" & .Range("C2:C" & derlig).Address
PlgCrit = .Name & "!" & .Range("D2:D" & derlig).Address
Crit = """=7*"""
End With

'PlgCrit est perçu comme du texte
MsgBox Evaluate("=SUMIFS(" & PlgSomme & "," & _
PlgCrit & "," & Crit & ")")

'PlgCrit peut-être numérique, texte ou alphanumérique.
'Si cette formule était inscrite dans une cellule, il faudrait la
'valider avec "Maj + Ctrl + Enter" (formule matricielle). Elle
bouffe beaucoup de ressources si les plages sont importantes...

MsgBox Evaluate("SUM(IF(MID(" & PlgCrit & ",1,1)=""7""," & PlgSomme & "))")

End Sub
'-----------------------------------------


MichD
---------------------------------------------------------------

Bonjour MichD,

'PlgCrit est perçu comme du texte
MsgBox Evaluate("=SUMIFS(" & PlgSomme & "," & _
PlgCrit & "," & Crit & ")")
'PlgCrit peut-être numérique, texte ou alphanumérique.
'Si cette formule était inscrite dans une cellule, il faudrait la
'valider avec "Maj + Ctrl + Enter" (formule matricielle). Elle
bouffe beaucoup de ressources si les plages sont importantes...
MsgBox Evaluate("SUM(IF(MID(" & PlgCrit & ",1,1)=""7""," & PlgSomme &
"))")

Seule cette dernière formule bouffe beaucoup de ressources ? Ou la première
en consomme beaucoup également ?

J'ai essayé les deux et j'arrive au même résultat dans les deux cas. Donc si
la première est moins gourmande je la prendrais.

Merci de votre aide

@+
HD