VBA Utiliser une Formule plutôt que de récupérer le résultat

Bonjour à Tous,

Utilisateur très occasionnel des formulaires et du vba j'ai un petit problème dont je n'ai pas trouvé la solution.

Dans le code ci-dessous, qui fonctionne, je veux récupérer la valeur des variables "tdp" et "trp" en fonction de la variable cpt.

à ce jour elles sont récupérée dans des cellules qui contiennent chacune une formule ( Q1 contient la formule "=SOMME.SI.ENS(E:E;B:B;"1.25%";J:J;"x") " )

Cette solution m'oblige à avoir dans la feuille "Resultats" quatre formules en N1, O1, Q1, et R1

Les variables sont déclarée en début de code, cpt est de type String, les autres de type Currency

cpt = Me.ComboBox1.Value

Select Case cpt
Case "1.25%"
    With Worksheets("SuiviBancaire")
    Lign = .Range("G" & Rows.Count).End(xlUp).Row + 1 'Pour placer le nouvel enregistrement à la première ligne du tableau "1.25%" non vide
    sdp = .Range("G" & Rows.Count).End(xlUp).Value 'récupérer le dernier solde rapproché du "1.25%"
    End With
    With Worksheets("Resultats")
    tdp = .Range("Q1").Value ' formule de Q1 : "  =SOMME.SI.ENS(E:E;B:B;"1.25%";J:J;"x")  "
    trp = .Range("R1").Value ' formule de R1 : "  =SOMME.SI.ENS(F:F;B:B;"1.25%";J:J;"x")  "
    End With

Case "0.2%"
    With Worksheets("SuiviBancaire")
    Lign = .Range("Q" & Rows.Count).End(xlUp).Row + 1 'Pour placer le nouvel enregistrement à la première ligne du tableau "0.2%" non vide
    sdp = .Range("Q" & Rows.Count).End(xlUp).Value 'récupérer le dernier solde rapproché du "0.2%"
    End With
    With Worksheets("Resultats")
    tdp = .Range("N1").Value ' formule de N1 : "  =SOMME.SI.ENS(E:E;B:B;"0.2%";J:J;"x")  "
    trp = .Range("O1").Value ' formule de O1 : "  =SOMME.SI.ENS(F:F;B:B;"0.2%";J:J;"x")  "
    End With
Case Else
    MsgBox "erreur dans le choix du compte"
End Select

Je souhaiterais ne plus avoir ces formules dans la feuille, mais obtenir quelque chose qui ressemblerait au code suivant, avec une formule qui utilise directement la variable cpt :

cpt = Me.ComboBox1.Value

Select Case cpt
With Worksheets("SuiviBancaire")
Case "1.25%"
      Lign = .Range("G" & Rows.Count).End(xlUp).Row + 1 'Pour placer le nouvel enregistrement à la première ligne du tableau "1.25%" non vide
    sdp = .Range("G" & Rows.Count).End(xlUp).Value 'récupérer le dernier solde rapproché du "1.25%"

Case "0.2%"
    Lign = .Range("Q" & Rows.Count).End(xlUp).Row + 1 'Pour placer le nouvel enregistrement à la première ligne du tableau "0.2%" non vide
    sdp = .Range("Q" & Rows.Count).End(xlUp).Value 'récupérer le dernier solde rapproché du "0.2%"

Case Else
    MsgBox "erreur dans le choix du compte"
End Select
End With

 With Worksheets("Resultats")
    tdp = '  résultat  de la formule  =SOMME.SI.ENS(E:E;B:B;cpt;J:J;"x")
    trp =  ' résultat de la formule   =SOMME.SI.ENS(F:F;B:B;cpt;J:J;"x") 
 End With

Je suis sur que la solution doit être évidente, mais là elle m'échappe.

Merci pour vos réponses.

Bonjour,

je ne pense pas être apte à t'aider , mais 2 choses:

1- pourquoi ne pas mettre ton fichier ou au moins une copie anonymisée ?

2 - somme.si.ens sur une colonne entière est il raisonnable ? je crois savoir que ça risque de ralentir , autant s'arrêter à la dernière ligne occupée

P.

Bonjour Patrick1957

Le fichier sur lequel je travaille (ci-joint) doit à terme se rajouter à un autre fichier.

Dans cet autre fichier la feuille "Resultat" affiche les réponses à une requête, et selon la requête ou la période de l'année à laquelle elle est faite, le nombre de ligne est très variable. Effectivement en utilisant la formule directement dans le code je pourrais peut-être limiter le nombre de lignes visitées

re,

c'est bien comme je le pensais, je ne saurais t'aider (je ne suis pas comptable °_°) mais j'ai néanmoins l'une ou l'autre remarque:

Quel est l'intéret de mettre une colonne "type" qui est soit "dépense" soit "recette" puisque tu as déjà une colonne pour ça ?

Pourquoi une colonne vide au milieu du tableau ?

Pourquoi un tableau sans avoir des titres à TOUTES les colonnes (colonne K et L ) ?

Je me demande si un TCD (voire 2) ne pourrait pas faire tes affaires ?

D'autres ici sauront certainement mieux appréhender le problème

P.

@Patrick1957

Je suis bien d'accord, il y a dans le fichier d'origine des informations redondantes, d'ailleurs les formules en N1 à R1 ne sont pas dans le fichier d'origine.

Et je n'ai pas la main sur ce fichier là, pour l'instant je doit faire avec cette page "Resultats", et j'aimerai autant ne pas y coller les formules. D'où ma question initiale... Qui pour l'instant ne semble inspirer personne.

Pour info le code fait parti d'un formulaire qui recense les opérations pointées dans "Resultats, fait le total "dépenses" ", le total "recettes" calcul le résultat et le solde théorique à nouveau, et qui effectue le rapprochement avec le relevé de compte, tout en différenciant s'il s'agit du compte A (1.25%) ou du compte B(0.2%)

A l'origine une requête est faite qui extrait toute les opérations non pointées du compte voulu de la base de donnée.

Ensuite chaque opération du relevé de compte doit être pointée manuellement (un "x" en J), puis le formulaire est lancé.

Comme le choix du compte dans la requête d'origine est une option, la formule revérifie la colonne B.

Si les soldes correspondent, le pointage est enregistré définitivement dans la BDD.

La feuille "Resultats" est réinitialisée lors de nouvelles requêtes.

Bonjour,

Je vois le sujet descendre dans les profondeur du classement...

Je pense que la solution doit être liée à la syntaxe de la fonction SumIfs, mais j'ai du mal à trouver des sujets me permettant de l'utiliser.

En tous cas aucun de mes essais ne marche.

Pour info j'ai trouvé une solution.

Voici le code :

Dim Li as integer
Dim Dep As Range
Dim Rec As Range
Dim Pointage As Range
Dim Compte As Range

With Worksheets("SuiviBancaire")

Select Case cpt
    Case "1.25%"
        Lign = .Range("G" & Rows.Count).End(xlUp).Row + 1 'Pour placer le nouvel enregistrement à la première ligne du tableau "1.25%" non vide
        sdp = .Range("G" & Rows.Count).End(xlUp).Value 'récupérer le dernier solde rapproché du "1.25%"

    Case "0.2%"
        Lign = .Range("Q" & Rows.Count).End(xlUp).Row + 1 'Pour placer le nouvel enregistrement à la première ligne du tableau "0.2%" non vide
        sdp = .Range("Q" & Rows.Count).End(xlUp).Value 'récupérer le dernier solde rapproché du "0.2%"

    Case Else
        MsgBox "erreur dans le choix du compte"
End Select

End With

 With Worksheets("Resultats")
    Li= .Range("B" & Rows.Count).End(xlUp).Row
    set Dep = .Range("E2:E" & Li)
    Set Rec = .Range("F2:F" & Li)
    Set Pointage = .Range("J2;J" & Li)
    Set Compte = .Range("B2:B" & Li)
 tdp = Application.SumIfs(Dep, Compte, cpt, Pointage, "x") '  résultat  de la formule  =SOMME.SI.ENS(E:E;B:B;cpt;J:J;"x")
   trp =  Application.SumIfs(Rec, Compte, cpt, Pointage, "x") ' résultat de la formule   =SOMME.SI.ENS(F:F;B:B;cpt;J:J;"x") 
End With

Pour répondre à la question de @Patrick1957, la valeur de la colonne "Type" sert lors de la saisie initiale des opérations à déterminer si se sont des recettes ou des dépenses, et à inscrire le montant de l'opération dans la colonne correspondante.