Modif de plage de formule complexe

[Temjeh]

Bonjour et merci d'avance

J'ai une macro qui fait plusieurs opérations dont une qui insére en activecell (ici en E62 test sur plage en haut E2:E61) qui est toujours en col E ...=SI(SOMMEPROD(NB.SI(List;E2:E61));"";"Aucun"):

...
insère ligne
...
ActiveCell.FormulaR1C1 = _
"=IF(SUMPRODUCT(COUNTIF(List,R[-60]C:R[-1]C)),"""",""Aucun"")"
...

J'ai 23000 cell a tester et y inclure ce genre de formule. Le prob est que E2:E61 est variable! et c'est celle-là que je doit écrire a la main car le reste de la formule ne change pas

Pour ex ici la dernière cell a vérifier est facile a trouvé car elle se trouve toujours cell du dessus

ActiveCell.FormulaR1C1 = _
"=IF(SUMPRODUCT(COUNTIF(List,R[-60]C:Activecell.offset(-1,0))),"""",""Aucun"")"

Ex: si l'activecell est E720 et qu'il y a une formule la plus proche vers le haut en E702 la plage à tester est E703:E719 ......=SI(SOMMEPROD(NB.SI(List;E703:E719));"";"Aucun") car en E702 il y a une formule comme celle-ci qui test sur plage plus haut


Ce que j'aimerais:

i=Activecell.offset(-1,0).adresse
j=Activecell.offset(-dernière sans formule,0).adresse

ActiveCell.FormulaR1C1 = _
"=IF(SUMPRODUCT(COUNTIF(List,i:j)),"""",""Aucun"")"




Pour la cell de départ ca serait la dernière cell vers le haut de l'activecell qui na pas de formule dedans à la cell du dessus

Merci beaucoup pour une idée et plus tard si je ne suis pas clair je ferai une demo

Temjeh

 

[Compte Supprimé 979]

Re : Modif de plage de formule complexe

Salut Temjeh,

Pourquoi ne pas utiliser : ActiveCell.FormulaLocal

Ce qui te permettrai d'écrire la formule très clairement :

 Dim NLig1 As Integer, NLig2 As Integer
  NLig1 = 2: NLig2 = 61
  ActiveCell.FormulaLocal = "=SI(SOMMEPROD(NB.SI(List;E" & NLig1 & ":E" & NLig2 & "));"""";""Aucun"")"

NLig1 et NLig2 seront juste à calculer selon ce que tu veux faire !

A+

 

[Temjeh]

Re : Modif de plage de formule complexe

Ok merci pour ce conseil

Dim NLig1 As Integer, NLig2 As Integer
NLig1 = 2
NLig2 = Activecell.offset(-1,0).row

ActiveCell.FormulaLocal = "=SI(SOMMEPROD(NB.SI(List;E" & NLig1 & ":E" & NLig2 & "));"""";""Aucun"")"

Il me manque juste le NLig1 ...une genre de boucle qui me test les cells au dessus de l'activecell et je veut la row de la ligne en dessous de la première qui a une formule (.row-1)

Merci

Temjeh

 

[Temjeh]

Re : Modif de plage de formule complexe

Merci je doit partir mais j'ai une solution pour la cell du bas de la première avec formule

For i = ActiveCell.Row To 1 Step -1
If Range("E" & i).FormulaLocal <> "" Then
NLig1 =Activecell.offset(1,0).row
Exit Sub
End If
Next i

A+

Temjeh

 

[Temjeh]

Re : Modif de plage de formule complexe

Rebonsoir
Voilà la final:

J'ai trouvé une pass en attendant mieux ...je delete les valeur de List donc la formule marque automatiquement Aucun (car aprés la première formule il arrète à la formule plus haute et test ...If Range("E" & i).Value = "Aucun" Then
C'est très bon mais jaimerais le vrai true ...merci

For i = ActiveCell.Row To 1 Step -1
If Range("E" & i).FormulaLocal <> "" Then
NLig1 = i + 1
Exit For
End If
Next i
NLig2 = ActiveCell.Offset(-1, 0).Row
ActiveCell.FormulaLocal = "=SI(SOMMEPROD(NB.SI(List;E" & NLig1 & ":E" & NLig2 & "));"""";""Aucun"")"

Le prob est que Range("E" & i).FormulaLocal <> "" ne test pas si il y a formule mais si cell est vide

Merci pour une dernière idée

A+

Temjeh

 

[Gruick]

Re : Modif de plage de formule complexe

Bonjour Temjeh,
Vu l'heure du dernier message, je vais te réveiller...!
J'ai peut être une solution que voici

Private Sub CommandButton1_Click()
For i = ActiveCell.Row [COLOR=red]- 1[/COLOR] To 1 Step -1
    If Range("E" & i).FormulaLocal [COLOR=red]=[/COLOR] "" Then
    [COLOR=red]ElseIf Range("E" & i) = "Aucun" Then
[/COLOR]    NLig1 = i + 1
    Exit For
    End If
Next i
NLig2 = ActiveCell.Offset(-1, 0).Row
[COLOR=red]If NLig1 = "" Then NLig1 = 1
[/COLOR]ActiveCell.FormulaLocal = "=SI(SOMMEPROD(NB.SI(List;E" & NLig1 & ":E" & NLig2 & "));"""";""Aucun"")"
End Sub

ça reste artisanal mais ça marche.

Alors, évidemment si je mets un des membres de la liste, plus aucune formule, mais ça, c'est une autre histoire, comme disait Kipling.

Bonne journée,
Gruick

 

[Temjeh]

Re : Modif de plage de formule complexe

Merci beaucoup

Effectivement jeme réveil ...Montréal 7AM nuageux

Ma boucle fonctionne trés bien si je supprime temporairement mes valeurs de List

On oublie tous les messages avant.

Pour plus simple comment tester dans une cell si il y a une formule ou une valeur écrite qui ne vient pas d'une formule!!! et après je l'insèrerai moi-même si c'est possible.

Merci pour toute suggestion

Temjeh

 

[pierrejean]

Re : Modif de plage de formule complexe

bonjour Temjeh

pour tester

if left(cstr(cel.formula),1)="=" then
 ' il y a une formule
end if

 

[Gruick]

Re : Modif de plage de formule complexe

Génial PierreJean,
J'y ai pensé, j'ai oublié le Cstr et le .Formula

A part ça, se perdre dans Lyon, c'est éprouvant, il y a des panneaux une fois que tu as trouvé la bonne route, sinon, tintin...mais à Paris, c'est l'inverse, tu n'as pas le temps de lire tellement il y a d'indications. Bref, c'est à vous dégoûter de quitter son pré carré pour partir à l'aventure.
Ceci dit, il y a des GPS...

Allez, amitiés à vous tous

Gruick

 

[Temjeh]

Re : Modif de plage de formule complexe

Merci beaucoup pierrejean

Comment le mettre dans ma démo(avant de la mettre dans ma vrai feuil)

Private Sub CommandButton1_Click()
For i = ActiveCell.Row To 1 Step -1
If Range("E" & i).FormulaLocal <> "" Then
NLig1 = i + 1
Exit For
End If
Next i
NLig2 = ActiveCell.Offset(-1, 0).Row
ActiveCell.FormulaLocal = "=SI(SOMMEPROD(NB.SI(List;E" & NLig1 & ":E" & NLig2 & "));"""";""Aucun"")"
End Sub

A+

Temjeh

 

[pierrejean]

Re : Modif de plage de formule complexe

bonjour Temjeh

en supposant que j'aie compris
et sous reserve de test

Private Sub CommandButton1_Click()
For i = ActiveCell.Row To 1 Step -1
If Left(Cstr(Range("E" & i).FormulaLocal) ,1)= "=" Then
NLig1 = i + 1
Exit For
End If
Next i
NLig2 = ActiveCell.Offset(-1, 0).Row
ActiveCell.FormulaLocal = "=SI(SOMMEPROD(NB.SI(List;E" & NLig1 & ":E" & NLig2 & "));"""";""Aucun"")"
End Sub

ps: en cas de probleme un petit fichier serait le bienvenu

 

[Temjeh]

Re : Modif de plage de formule complexe

Bonjour à tous Montréal 5:32 AM ...soleil

Oui merci beaucoup pierrejean c'est exactement ca! Le tout se fait automatiquement. Perfection!!!

Merci Gruick pour tes suggestions qui mon fait avancer avec ce code

Et enfin je renomme ma déclaration de variable(Merci Bruno)

A++

Temjeh