XL 2016 Comparer les valeurs d'une plage de cellule à une ligne .

[album40]

Bonsoir le forum

J'ai une feuille Excel sur laquelle
J'aimerais comparer les valeurs des cellules ("B5:K19") aux valeurs des cellules ("B2:K2").
Ensuite copier ces valeurs (ligne par ligne) qui répondent à mes critères ( de comparaison) dans les cellules ("N5:W19").
Critères de Choix des valeurs : Les valeurs ("B5:K5") seront choisi si
Valeurs ("B5:K19") = Valeurs ("B2:K2")
Valeurs ("B5:K19") = Valeurs ("B2:K2") +1
Valeurs ("B5:K19") = Valeurs ("B2:K2") -1

Je prend l'exemple réel de la Cellule B5=105 comparer aux cellules ("B2:K2")
j'ai le résultat (105) dans les cellules ("N5:W5")


J'ai insérer un code mais je n'arrive pas à l'adapter à tous les cellules ("B2:K2)
aidez moi SVP.

 

[mapomme]

Bonsoir @album40 ,

Inutile d'invoquer du VBA, une simple formule en N5 basée sur un SOMMEPROD suffit.
Cette formule sera à recopier vers la droite et vers le bas jusqu'en W19.

Comme apparemment c'est un exercice, je vous laisse un peu chercher la formule :

=SI(SOMMEPROD( (B5 >= (xxx-1) ) * (B5 <= (xxx+1) ) ) > zzz ; B5; "")

Il suffit de trouver xxx et zzz...

Pour la même chose en VBA:
il suffira de placer la formule ci-dessus dans les cellules N5:W19 puis de transformer les résultats de ces formules en valeurs.

Sub CelluleOK()
   With Feuil1
      .Range("n5").FormulaLocal = "=SI(SOMMEPROD( (B5 >= (xxx-1) ) * (B5 <= (xxx+1) ) ) > zzz ; yyy ; """")"
      .Range("n5").Copy
      .Range("n5:w19").PasteSpecial xlPasteFormulas
      .Range("n5:w19") = Range("n5:w19").Value
   End With
End Sub

 

[Jacky67]

Bonsoir le forum

J'ai une feuille Excel sur laquelle
J'aimerais comparer les valeurs des cellules ("B5:K19") aux valeurs des cellules ("B2:K2").
Ensuite copier ces valeurs (ligne par ligne) qui répondent à mes critères ( de comparaison) dans les cellules ("N5:W19").
Critères de Choix des valeurs : Les valeurs ("B5:K5") seront choisi si
Valeurs ("B5:K19") = Valeurs ("B2:K2")
Valeurs ("B5:K19") = Valeurs ("B2:K2") +1
Valeurs ("B5:K19") = Valeurs ("B2:K2") -1

Je prend l'exemple réel de la Cellule B5=105 comparer aux cellules ("B2:K2")
j'ai le résultat (105) dans les cellules ("N5:W5")


J'ai insérer un code mais je n'arrive pas à l'adapter à tous les cellules ("B2:K2)
aidez moi SVP.

Bonjour,
Une proposition sans VBA en PJ

**Hello mapomme

 

[Jacky67]

Bonsoir @album40 ,

Inutile d'invoquer du VBA, une simple formule en N5 basée sur un SOMMEPROD suffit.
Cette formule sera à recopier vers la droite et vers le bas jusqu'en W19.

Comme apparemment c'est un exercice, je vous laisse un peu chercher la formule :

=SI(SOMMEPROD( (B5 >= (xxx-1) ) * (B5 <= (xxx+1) ) ) > zzz ; B5; "")

Il suffit de trouver xxx et zzz...

Pour la même chose en VBA:
il suffira de placer la formule ci-dessus dans les cellules N5:W19 puis de transformer les résultats de ces formules en valeurs.

Sub CelluleOK()
   With Feuil1
      .Range("n5").FormulaLocal = "=SI(SOMMEPROD( (B5 >= (xxx-1) ) * (B5 <= (xxx+1) ) ) > zzz ; yyy ; """")"
      .Range("n5").Copy
      .Range("n5:w19").PasteSpecial xlPasteFormulas
      .Range("n5:w19") = Range("n5:w19").Value
   End With
End Sub

Hello mapomme
Ou encore

Sub comparai2nbresJJ()
    [n5:w19].Formula = "=IF(OR(COUNTIF($B$2:$K$2,B5),COUNTIF($B$2:$K$2,b5+1),COUNTIF($B$2:$K$2,b5-1)),b5,"""")"
    [n5:w19].Value = [n5:w19].Value
End Sub

 

[album40]

Merci mapomme et Jacky67

C'est bien ce que je voulais.

Mais dites moi pouvez vous m'orienter pour que je puisse maîtriser aussi rapidement ces formules?
je me perd des fois. Je veux identifier à quelle moment il faut utiliser les codes VBA ou formules.

​

 

[mapomme]

Je veux identifier à quelle moment il faut utiliser les codes VBA ou formules.

Pour ma formule en N5 (fichier .xlsx):

=SI(SOMMEPROD( (B5 >= ($B$2:$K$2-1)) * (B5 <= ($B$2:$K$2+1))  )>0;B5;"")

Pour le code (dans module1 du fichier .xlsm) :

Sub CelluleOK()
   With Feuil1
      .Range("n5").FormulaLocal = "=SI(SOMMEPROD( (B5 >= ($B$2:$K$2-1)) * (B5 <= ($B$2:$K$2+1))  )>0;B5;"""")"
      .Range("n5").Copy
      .Range("n5:w19").PasteSpecial xlPasteFormulas
      .Range("n5:w19") = Range("n5:w19").Value
   End With
End Sub

Je veux identifier à quelle moment il faut utiliser les codes VBA ou formules.

Il n'y a pas de réponse nette. C'est en fonction de la difficulté à trouver une formule ou non, de son penchant 'naturel' à élaborer des formules ou concevoir du code. Dans certain situations, le VBA est interdit par l'administrateur au sein de l'entreprise.
Si vous parcourez le forum, vous vous rendrez compte que les solutions pour une même question sont à la fois par formules pour certains répondants et par VBA pour d'autres répondants.

nota : bonsoir @Jacky67
nota : bonne-nuit @Staple1600

 

[album40]

J'ai bien compris
Merci pour votre disponibilité.

 

[Staple1600]

Re

=>Jacky67
Ou encore

Sub comparai2nbresJJ()
[n5:w19] = "=IF(OR(COUNTIF($B$2:$K$2,B5),COUNTIF($B$2:$K$2,b5+1),COUNTIF($B$2:$K$2,b5-1)),b5,"""")"
[n5:w19] = [n5:w19].Value
End Sub

NB: C'est juste parce j'avais besoin d'écrire une dernière fois du VBA avant d'aller au dodo
(C'est un rituel: un petit coup de VBA, un petit coup de dentifrice, un petit pissou et sous la couette
Pas d'inquiétude: oui, je me lave bien les mains après la miction, et je dors avec mon masque
(Ca m'entraine pour le prochain confinement)

 

[album40]

merci Staple1600

​