transformer formule SOMMEPROD en VBA Résolu/Fermé

Question

Bonjour
peux quelqu'un m'aider pour effectué une fonction SOMMEPROD en vba pour un tableau de statistique mensuel via une date horizontal de ("D3:AH3") et des nationalité vertical de("C4:C39"), j'avais fais cette formule suivante:=SOMMEPROD((Tableau3[Nat]=Feuil5!$C4)*($D$3>=Tableau3[Arrivée])).
mais je veux faire ça en vba pour que mon fichier excel soit bien professionnelle.
sachant que les données sont dans la feuil("Fiche_Client") et le transfert soit dans la feuil("Arrivée").
merci en avance ce fichier pour mon travail d’hôtel.
ci joint mon fichier de test.
https://www.cjoint.com/c/IFisRyLN8gK

Frenchie83 · Answer

Bonjour,

Pourquoi parlez-vous de SOMMEPROD alors que vous utilisez NB.SI.ENS qui va très bien d'ailleurs?

    Range("D4:AH42").FormulaR1C1 = "=COUNTIFS(Fiche_Client!C11,R3C,Fiche_Client!C9,RC3)"

Cdlt

Raymond PENTIER · Answer

Bonsoir.

Je ne connais pas bien VBA, et je ne peux pas t'aider pour ta macro. Cependant j'en sais suffisamment pour t'assurer que tu ne retirerais aucun avantage à remplacer tes formules classiques pour du VBA, et que tu resterais le seul à penser que ton fichier serait devenu "bien professionnel", ce qui est inexact.

Par contre tu peux optimiser ta formule
=NB.SI.ENS(Tableau3[Arrivée];Arrivées!D$3;Tableau3[Nat];Arrivées!$C4)
en l'écrivant 
=NB.SI.ENS(Tableau3[Arrivée];D$3;Tableau3[Nat];$C4)
ou même
=NB.SI.ENS(T[Arrivée];D$3;T[Nat];$C4)
si tu remplaces le nom Tableau3 par T, 
vu qu'il y a quand même 1209 fois cette formule dans chacune de tes 3 dernières feuilles !

Tu pourrais en profiter pour supprimer les noms inutiles de cette liste : chambres, Clients, Codes, compta, Crit1, Crit2, Crit3, Extraire, invoices, names, Noms, prix, taxe et tpt

Frenchie83 · Answer

RE,

votre formule traduite en VBA
Range("D4:AH42").FormulaR1C1 = "=COUNTIFS(Tableau3[Arrivée],Arrivées!R3C,Tableau3[Nat],Arrivées!RC3)"

ou avec la fonction équivalente VBA
Sub Formule()    Application.ScreenUpdating = False    Set f1 = Sheets("Fiche_Client")    Set f2 = Sheets("Arrivées")    For L = 4 To 42        For c = 4 To 34            f2.Cells(L, c) = Application.WorksheetFunction.CountIfs(f1.Range("K6:K10000"), f2.Cells(3, c), f1.Range("I6:I10000"), f2.Cells(L, 3))        Next c    Next L    Set f1 = Nothing    Set f2 = NothingEnd Sub

Cdlt

belba.yahya · Answer

Bonjour Frenchie
vous vous souvenez,vous m'avez aider de transformé une formule en vba avec succés
est ce possible de me donner un idé pour cela d'une formule pour faire countifs entre deux dates

j'ai crée ce code mais ne m'a rien donné: 

Application.ScreenUpdating = False
Set f1 = Sheets("Client")
Set F3 = Sheets("NUITEE")
For L = 5 To 39
For c = 4 To 34
F3.Cells(L, c) = Application.WorksheetFunction.CountIfs(f1.Range("D2:D10000"), "<" & F3.Cells(4, c) - f1.Range("C2:C10000"), "=" & F3.Cells(4, c), f1.Range("B2:B10000"), F3.Cells(L, 3))
Next c
Next L
Set f1 = Nothing
Set F3 = Nothing

Frenchie83 · Answer

Bonjour,

Avez-vous créer la formule dans la feuille excel (dans la première cellule et première colonne du tableau)?

Si elle fonctionne bien, allez dans la barre de formule, balayez-là avec la souris puis faire CTRL+ C (copier)

Allez dans la feuille VBA et dans les boucles For next, remplacez
F3.Cells(L, c) = Application.WorksheetFunction.CountIfs(f1.Range("D2:D10000"), "<" & F3.Cells(4, c) - f1.Range("C2:C10000"), "=" & F3.Cells(4, c), f1.Range("B2:B10000"), F3.Cells(L, 3))
 par
F3.Cells(L, c).FormulaLocal= suivi d'un  CTRL + V
Ce qui vous copiera la formule identique à celle écrite dans excel.

Cdlt

belba.yahya · Answer

je vous remerie carrément Mr Frenchie pour la réponse favorable,ensuite
j'ai essayé de faire le code comme il est mais je ne sais pas pourquoi n'a pas fonctionné.
je vous joint mon fichier pour regarder si il y a une possibilité de l'activer.
 https://www.cjoint.com/c/JAtnj20OZDD

belba.yahya · Answer

c'est la formule exact que j'ai crée:
SOMMEPROD((Feuil1!B2:B10000=Feuil3!$C10)*(Feuil3!D$4>=Feuil1!C2:C10000)*(Feuil3!D$4<Feuil1!D2:D10000))
je veux la convertir en VBA si possible

Frenchie83 · Answer

Bonjour,

Vous n'avez pas fait ce que je vous ai indiqué.
Il faut récupérer la formule créée sous excel et l'ajouter à la suite de "F3.Cells(L, c).FormulaLocal="
Ce qui donne:
F3.Cells(L, c).FormulaLocal=SOMMEPROD((Feuil1!B2:B10000=Feuil3!$C10)*(Feuil3!D$4>=Feuil1!C2:C10000)*(Feuil3!D$4<Feuil1!D2:D10000)) 

Cdlt

Frenchie83 · Answer

Voici le fichier
https://mon-partage.fr/f/c9JpoG5m/

le code pour la macro "countbetween_twodates"
Sub countbetween_twodates()  ' ceci à rectifier
    Application.ScreenUpdating = False
    Sheets("Feuil3").Range("D5:AH39").FormulaR1C1 = "=IFERROR(IF(AND(INDIRECT(""Feuil1!$C""&MATCH(RC3,Feuil1!C2,0))<=R4C,INDIRECT(""Feuil1!$D""&MATCH(RC3,Feuil1!C2,0))>=R4C),1,0),"""")"
    Sheets("Feuil3").Range("D5:AH39").Value = Sheets("Feuil3").Range("D5:AH39").Value
    Application.ScreenUpdating = True
End Sub

Cdlt

belba.yahya · Answer

Merci Frenchie pour tout cela,presque tout etait excelent,sauf dans le premier ligne"Espagne" doit donner le resultat "2" et pas "1"
car dans la feuil1 il y a 2 espagnes du:03/01 au 06/01 est 1 espagne du 6/01 au 24/1
je vous remercie pour tout,je vais essayer de faire quelque chose.

Frenchie83 · Answer

Toutes mes excuses, je n'avais pas vu qu'il pouvait y avoir plusieurs fois le même nom. Voici le correctif
Sub countbetween_twodates()  ' ceci à rectifier
    Application.ScreenUpdating = False
    Sheets("Feuil3").Range("D5:AH39").FormulaR1C1 = "=SUMPRODUCT((Feuil1!R2C2:R10000C2=RC3)*(R4C>=Feuil1!R2C3:R10000C3)*(R4C<Feuil1!R2C4:R10000C4))"
    Sheets("Feuil3").Range("D5:AH39").Value = Sheets("Feuil3").Range("D5:AH39").Value
    Application.ScreenUpdating = True
End Sub

Cdlt

belba.yahya · Answer

merci infiniment Monsieur Frenchie,c'est très bien,comme je le souhaitais.

Transformer formule SOMMEPROD en VBA

12 réponses

Discussions similaires