XL 2021 Transformer une formule en vba

[gege5959]

Bonjour,

Je suis débutant et je cherche à transcrire la formule suivante en vba mais j'ai toujours des messages d'erreur alors que la formule fonctionne dans une cellule
=SOMME(NB.SI.ENS(A1:E1;G1:J1))
Le but étant de compter le nombre de doublons dans une plage avec pour critère une autre plage
Cette formule sera intégrée à 2 boucles si bien qu'elle pourrait finir par exemple par =SOMME(NB.SI.ENS(A500:E500;G300:J300))

Je l'ai écrite comme suit:
com1 = activeWorksheetFunction.somme(activeWorksheetFunction.nb.si.ens(Sheets("sol2").Range(Cells(i, j), Cells(i, j + 4)), Sheets(feu1).Range(Cells(posi, colref1 ), Cells(posi, colref1 +3))))

Message d'erreur: «erreur définie par l'application ou par l'objet»

Merci d'avance pour votre éclairage

 

[sylvanu]

Bonjour géré,
Le vba ne connaît que l'anglais.
Essayez de replacer Somme par Sum, et Nb.si.ens par Countifs.

 

[gege5959]

Bonjour géré,
Le vba ne connaît que l'anglais.
Essayez de replacer Somme par Sum, et Nb.si.ens par Countifs.

Bonjour,

Ca ne fonctionne pas. Toujours le même message d'erreur.

 

[TooFatBoy]

Bonjour,

J'y vais de ma question en mousse... : pourquoi faire par macro ce qui semble pouvoir être fait par formule ?

 

[sylvanu]

Re,
Je pense que la formule pour "com1" est une formule matricielle, elle ne peut pas être traitée directement en VBA.
Je pense qu'il vous faudra passer par des boucles. Ou par une cellule intermédiaire comme par ex :

Sub Essai()
    [A1].FormulaArray = "=(SUM(COUNTIFS('sol2'!A500:E500,'feu1'!G300:J300)))"   ' Calcul formule matricielle
    com1 = [A1]                                                                 ' Récupération valeur
    [A1] = ""                                                                   ' Effacement cellule
End Sub

Si les plages sont variables il faut construire la formule avec les bonnes valeurs.
Ou peut être existe t-il un moyen plus simple. Wait and see.

 

[sylvanu]

Re,
ChatGPT propose une fonction avec deux boucles :

Function SommeNB_SI_ENS(plage1 As Range, plage2 As Range) As Long
    Dim cell1 As Range
    Dim cell2 As Range
    Dim count As Long
    count = 0
    For Each cell1 In plage1
        For Each cell2 In plage2
            If cell1.Value = cell2.Value And Not IsEmpty(cell1.Value) Then
                count = count + 1
            End If
        Next cell2
    Next cell1
    SommeNB_SI_ENS = count
End Function

Pour l'appeler il suffit de faire :

com1=SommeNB_SI_ENS(plage1, plage2)

en ayant définit les plage1 et 2 précédemment.

Ou avec une seule boucle donnée par Gemini :

Function SommeNbSiEnsMatricielle(PlageCritere1 As Range, PlageCritere2 As Range) As Double
    ' Déclare une variable pour stocker la somme
    Dim total As Double
    ' Déclare une variable pour stocker le critère
    Dim critere As Range
    ' Initialise la somme
    total = 0
    ' Boucle sur chaque cellule de la deuxième plage de critères (G300:J300)
    For Each critere In PlageCritere2
        ' Ajoute à la somme le nombre de fois que le critère est trouvé dans la première plage (A500:E500)
        total = total + Application.WorksheetFunction.CountIf(PlageCritere1, critere.Value)
    Next critere
    ' Renvoie le résultat final
    SommeNbSiEnsMatricielle = total
End Function

Fonctions que je n'ai pas testé. Sans PJ trop complexe.

 

[gege5959]

Bonjour,

J'y vais de ma question en mousse... : pourquoi faire par macro ce qui semble pouvoir être fait par formule ?

Je trouve ca plus limpide. J'ai abandonné la programmation il y a quelques années et ca a du mal à revenir, tout en restant un amateur.

 

[gege5959]

Re,
Je pense que la formule pour "com1" est une formule matricielle, elle ne peut pas être traitée directement en VBA.
Je pense qu'il vous faudra passer par des boucles. Ou par une cellule intermédiaire comme par ex :

Sub Essai()
    [A1].FormulaArray = "=(SUM(COUNTIFS('sol2'!A500:E500,'feu1'!G300:J300)))"   ' Calcul formule matricielle
    com1 = [A1]                                                                 ' Récupération valeur
    [A1] = ""                                                                   ' Effacement cellule
End Sub

Si les plages sont variables il faut construire la formule avec les bonnes valeurs.
Ou peut être existe t-il un moyen plus simple. Wait and see.

Bonjour,
Dans ma formule com1 peut tout à fait prendre un autre nom. J'en veux pour preuve que le type de formulation fonctionne, comme dans une autre macro où j'utilise: ValMin = Application.WorksheetFunction.Min(Range(Cells(18, 1), Cells(31, 840))) . ValMin peut aussi bien s'appeler LaMinus.
Merci pour votre proposition de code. J'essaierai en rentrant tout à l'heure.

 

[sylvanu]

Dans ma formule com1 peut tout à fait prendre un autre nom.

Là n'est vraiment pas le problème.
Votre formule est matricielle, il est là le souci.
Dans votre exemple Valmin n'est pas matricielle, c'est pour ça qu'elle fonctionne.

 

[TooFatBoy]

Je trouve ca plus limpide. J'ai abandonné la programmation il y a quelques années et ca a du mal à revenir, tout en restant un amateur.

Justement, si tu as abandonner la programmation et que ça a du mal à revenir, autant utiliser une formule. 😉

Mais le camarade sylvanu va te résoudre ça. 👍

 

[sylvanu]

Mais le camarade @sylvanu va te résoudre ça.

C'est ce que fait le code du post #5.
Je met la formule en matriciel dans A1, et je récupère le résultat dans une variable, puis efface A1.
Je pense que le but était de faire la calcul dans une variable. Mais passer par une fonction perso est plus élégant.

 

[laurent950]

Bonsoir Le Forum

' "=SUM(COUNTIFS(R[497]C[-5]:R[497]C[-1],R[297]C[1]:R[297]C[4]))"'
Sub SommeCountIfsAvecVariables()
    Dim ligne1 As Long
    Dim ligne2 As Long
    Dim colDebut As Long
    Dim colFin As Long
    Dim formule As String
    
    ' Définition des variables
    ligne1 = 497   ' décalage de la première ligne pour COUNTIFS
    ligne2 = 297   ' décalage de la deuxième ligne pour les critères
    colDebut = -5  ' première colonne relative
    colFin = -1    ' dernière colonne relative
    
    ' Construction de la formule
    formule = "=SUM(COUNTIFS(R[" & ligne1 & "]C[" & colDebut & "]:R[" & ligne1 & "]C[" & colFin & "], R[" & ligne2 & "]C[1]:R[" & ligne2 & "]C[4]))"
    
    ' Affectation de la formule à la cellule active
    ActiveCell.FormulaR1C1 = formule
End Sub

 

[sylvanu]

Bonsoir Laurent,
A priori, le demandeur veut le résultat en variable, d'où le souci.

 

[laurent950]

A priori, le demandeur veut le résultat en variable, d'où le souci.

Sub Macro3()
    Dim rng1 As String
    Dim rng2 As String
    
    ' ' =SOMME(NB.SI.ENS(A1:E1;G1:J1))
    ' Adresse relative en notation A1
    rng1 = Range(Cells(1, 1), Cells(1, 4)).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    rng2 = Range(Cells(1, 7), Cells(1, 10)).Address(RowAbsolute:=False, ColumnAbsolute:=False)

    ' Formule en français avec ; comme séparateur
    ActiveCell.FormulaLocal = "=SOMME(NB.SI.ENS(" & rng1 & ";" & rng2 & "))"
End Sub

 

[AtTheOne]

bonsoir à toutes & à tous, bonsoir @gege5959

De cette façon, avec EVALUATE

Sub tst()
'...
     Rg1 = [A1:E1].Address(external:=True)
     Rg2 = [G1:J1].Address(external:=True)
     TT = Evaluate("Sum(CountIf(" & Rg1 & "," & rg2 & "))")
'...
End Sub

Chez moi cela fonctionne !
À bientôt