Transformer formule SOMMEPROD en VBA

Résolu/Fermé

belba.yahya Messages postés 112 Date d'inscription mercredi 2 janvier 2019 Statut Membre Dernière intervention 5 août 2020 - 8 juin 2019 à 20:46
belba.yahya Messages postés 112 Date d'inscription mercredi 2 janvier 2019 Statut Membre Dernière intervention 5 août 2020 - 20 janv. 2020 à 09:38

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

A voir également:

Transformer formule SOMMEPROD en VBA
Formule excel - Guide
Formule si et - Guide
Mise en forme conditionnelle formule si - Guide
Formule moyenne excel - Guide
Formule excel pour additionner plusieurs cellules - Guide

12 réponses

Réponse 1 / 12

Frenchie83 Messages postés 2240 Date d'inscription lundi 6 mai 2013 Statut Membre Dernière intervention 11 août 2023 337
20 janv. 2020 à 09:28

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

Réponse 2 / 12

Frenchie83 Messages postés 2240 Date d'inscription lundi 6 mai 2013 Statut Membre Dernière intervention 11 août 2023 337
9 juin 2019 à 04:05

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

belba.yahya Messages postés 112 Date d'inscription mercredi 2 janvier 2019 Statut Membre Dernière intervention 5 août 2020
9 juin 2019 à 13:33

merci beaucoup,ça a bien fonctionné,sauf je ne sais pas pourquoi ça devient très lent

Réponse 3 / 12

Raymond PENTIER Messages postés 58388 Date d'inscription lundi 13 août 2007 Statut Contributeur Dernière intervention 17 avril 2024 17 089
Modifié le 9 juin 2019 à 04:31

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

Réponse 4 / 12

Frenchie83 Messages postés 2240 Date d'inscription lundi 6 mai 2013 Statut Membre Dernière intervention 11 août 2023 337
9 juin 2019 à 09:08

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 = Nothing
End Sub

Cdlt

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 12

belba.yahya Messages postés 112 Date d'inscription mercredi 2 janvier 2019 Statut Membre Dernière intervention 5 août 2020
18 janv. 2020 à 13:36

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

Réponse 6 / 12

Frenchie83 Messages postés 2240 Date d'inscription lundi 6 mai 2013 Statut Membre Dernière intervention 11 août 2023 337
19 janv. 2020 à 11:05

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

Réponse 7 / 12

belba.yahya Messages postés 112 Date d'inscription mercredi 2 janvier 2019 Statut Membre Dernière intervention 5 août 2020
19 janv. 2020 à 14:11

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

Réponse 8 / 12

belba.yahya Messages postés 112 Date d'inscription mercredi 2 janvier 2019 Statut Membre Dernière intervention 5 août 2020
Modifié le 20 janv. 2020 à 01:51

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

Réponse 9 / 12

Frenchie83 Messages postés 2240 Date d'inscription lundi 6 mai 2013 Statut Membre Dernière intervention 11 août 2023 337
20 janv. 2020 à 06:36

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

Réponse 10 / 12

Frenchie83 Messages postés 2240 Date d'inscription lundi 6 mai 2013 Statut Membre Dernière intervention 11 août 2023 337
20 janv. 2020 à 07:47

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

Réponse 11 / 12

belba.yahya Messages postés 112 Date d'inscription mercredi 2 janvier 2019 Statut Membre Dernière intervention 5 août 2020
20 janv. 2020 à 09:06

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.

Réponse 12 / 12

belba.yahya Messages postés 112 Date d'inscription mercredi 2 janvier 2019 Statut Membre Dernière intervention 5 août 2020
20 janv. 2020 à 09:38

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

Discussions similaires

je veut créer des codes

exo pix

transformer un cable telephone en RJ45

Formule Excel : "SI" une cellule contient un mot, alors ?

"L'indice n'appartient à la sélection" + VBA

Discussions similaires

Newsletters