Conversion en VBA d'un somme.si matriciel

[choudoudou]

Bonjour,

Dans le fichier ci joint , j'utilise une formule qui me permet de faire une décrémentation en fonction de plusieurs critere . J'aimerais passer cette formule sous la forme VBA avec des boucles si possible.

"=SI(D19<>"";SI(D19-SOMME.SI($A9:$A18;$A19;E9:E18)>=C19;C19;D19-SOMME.SI($A9:$A18;$A19;E9:E18));0)"

Je suis débutant dans ce domaine et en programmation.

J'ai mis en piece jointe mon fichier de calcul qui sera plus parlant pour comprendre la formule à passer en VBA

Merci d'avance pour votre aide.

 

[choudoudou]

Re : Conversion en VBA d'un somme.si matriciel

Personne ne peut vraiment pas m'aider pour convertir cette formule sous la forme VBA.
Je lutte depuis deux jours mais je suis vraiment nul en programmation.

J'ai simplifié au maximum ce que je désirais mais mon fichier d'origine fait environ 40 000 lignes et Excel a du mal à calculer avec une simple formule.

merci d'avance

 

[JNP]

Re : Conversion en VBA d'un somme.si matriciel

Bonjour Choudoudou et bienvenue ,
Ce n'est pas que personne ne peut t'aider, mais ta question n'est pas claire, en tout cas pour moi ...
Ta formule te cause certainement, mais j'ai beau l'avoir disséquée, je n'ai pas vraiment compris ce qu'elle faisait... Et que veux-tu faire en VBA, une fonction pour remplacer la formule ? De l'avis des spécialistes d'XLD, il semblerait que VBA soit plus lent que les formules, donc où serait le gain ?
En utilisant les formules, as-tu essayé de stopper le calcul automatique avant de recopier ta formule vers le bas, puis remis le calcul automatique, afin que le calcul ne se fasse qu'après ta copie ?
Si c'est une procédure de traitement que tu souhaites, que doit-elle faire ? Tu parles de boucles sur 40 000 lignes, mais que va faire VBA, 40 000 boucles sur 40 000 lignes, multiplié par autant de tests, etc. je suis pas sûr que ça va se faire très vite... Le fichier peut-il être trié de façon à décrire une colonne et à chaque changement de valeur dans cette colonne, limiter le traitement à cette zone ?
Bref, un peu de littérature nous aidera à t'aider .
Bonne journée

 

[choudoudou]

Re : Conversion en VBA d'un somme.si matriciel

Merci pour ta réponse JNP,

J'ai refait mon fichier excel en essayant de vulgariser un maximum mon besoin. J'ai même simplifier la formule de départ en supprimant un "SI"

Ce que je veux faire en VBA c'est traduire cette formule avec des boucles While et IF,...

Pour répondre à ta question sur le calcul automatique, j'ai essayé mais cela me fait planter excel car mon fichier fait environ 50Mo avec d'autres formules aussi complexe.

Merci d'avance pour votre aide

 

[vbacrumble]

Re : Conversion en VBA d'un somme.si matriciel

Bonjour



C'est ce tu veux faire ?
(je suis parti de ton 1er fichier joint)

Sub Macro1()
[COLOR="Green"]'
' Macro1 Macro
' Macro enregistrée le 01/05/2009 par VBACrumble
'[/COLOR]
Dim formule$, i&
formule = _
"=IF(RC[-1]<>"""",IF(RC[-1]-SUMIF(R[-10]C1:R[-1]C1,RC1,R[-10]C:R[-1]C)>=RC[-2],RC[-2],RC[-1]-SUMIF(R[-10]C1:R[-1]C1,RC1,R[-10]C:R[-1]C)),0)"
dl = Range("A" & Rows.Count).End(xlUp).Row
For i = 20 To 100 [COLOR="Green"]'ici mettre le nombre de ligne désiré[/COLOR]
Cells(i, "E").FormulaR1C1 = formule
Next
End Sub

 

[michel_m]

Re : Conversion en VBA d'un somme.si matriciel

bonjour,

pour ta question de la transformation de ta formule en VBA:

Dim col_A As Range, col_E As Range

Set col_A = Range(Cells(9, 1), Cells(18, 1))
Set col_E = Range(Cells(9, 5), Cells(18, 5))

If Cells(19, 4) <> "" Then
MsgBox Application.Min(Cells(19, 3), Cells(19, 4) - Application.SumIf(col_A, Cells(19, 1), col_E))
End If
Set col_A = Nothing
Set col_E = Nothing

J'ai mis des "cells(y,x)" plutôt que des "range" car tu laisses entendre que tu veux boucler, mais comment c'est mystère et boule de gomme...

PS: je n'avais pas vu les 2 messages précédents...

 

[JNP]

Re : Conversion en VBA d'un somme.si matriciel

Re ,
Tu peux essayer ce code VBA

Sub test()
Dim I As Integer
For I = 1 To Range("A35356").End(xlUp).Row
Cells(I, 5).FormulaR1C1 = _
        "=IF(RC[-1]<>"""",IF(RC[-1]-SUMIF(R[-" & I - 1 & "]C1:R[-1]C1,RC1,R[-" & _
        I - 1 & "]C:R[-1]C)>=RC[-2],RC[-2],RC[-1]-SUMIF(R[-" & I - 1 & _
        "]C1:R[-1]C1,RC1,R[-" & I - 1 & "]C:R[-1]C)),0)"
Cells(I, 5) = Cells(I, 5).Value
Next I
End Sub

qui commence par saisir la formule dans la cellule E puis la remplace par la valeur obtenue.
Si ça plante, il est possible de faire un enregistrement automatique tous les 1000 lignes par exemple, mais ça va ralentir le temps de traitement.
Bon après-midi
Ajout : Complètement la tête dans le guidon, j'avais pas rafraîchi... Salut à tous, avec tout ça, ça devrait le faire...

 

[choudoudou]

Re : Conversion en VBA d'un somme.si matriciel

Bonjour,

Je vous remercie tous pour votre aide. les solutions que vous m'avez proposées, fonctionnement correctement et correspondent à mon besoin.

Etant débutant en VBA, j'ai cherché pendant 2 jours et vous me trouvez la solution en quelques heures. Merci encore , et je vais essayer de progresser en programmation