XL 2013 Définir une plage issue de 2 blocs non contigus

[Sebast]

Bonjour à toutes et à tous,
Je suis à la recherche d'un moyen qui me permettrait de définir un Range issu de deux blocs non contigus

En effet, dans le code ci-dessous (largement inspiré par l'aide précieuse du forum), j'utilise un range pour détecter un changement de valeur dans les cellules

Pour faire simple,
si en onglet "Saisie" colonne B on trouve une entrée identique à celle figurant en onglet "Activités" colonne A, alors on colle unité et prix correspondants
Jusque-là je sais faire
MAIS
en onglet "Saisie" colonne B, les valeurs proviennent de ce qu'il y a en "Activités" ou "Frais_généraux"
si bien que mon range de recherche, actuellement issu de l'onglet "Activités" ne prend pas ce qu'il y a en onglet "Frais_généraux"

Pour différentes raisons, je ne peux pas fusionner "Activités" et "Frais_généraux" (le présent exemple est épuré pour se focaliser sur le problème mais il y a d'autres implications qui font que Activités et Frais_généraux doivent rester 2 feuilles distinctes)

Donc ma question serait :

Peut-on créer un range issu de deux plages non consécutives ? Comment ? ou doit-on procéder tout autrement ?
J'ai cherché un peu partout (union ? …) mais je n'y arrive pas …

Quelqu'un a-t-il une idée comment définir ActivitesToutesColonnes qui pointeraient dans les 2 feuilles ?

Merci d'avance pour vos lumières

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim dernlig As Long
Dim lastline As Long
Dim ActivitesToutesColonnes As Range
Dim RubriquesDansSaisie As Range
Dim Cellule As Variant

dernlig = Sheets("Saisie").Range("B" & Rows.Count).End(xlUp).Row
Set RubriquesDansSaisie = Sheets("Saisie").Range("B2:B" & dernlig)

lastline = Sheets("Activités").Range("A" & Rows.Count).End(xlUp).Row
Set ActivitesToutesColonnes = Sheets("Activités").Range("A2:C" & dernlig)

If Not Intersect(Target, RubriquesDansSaisie) Is Nothing Then
        For Each Cellule In ActivitesToutesColonnes
            If Cellule = Target Then
                Target.Offset(0, 1) = Cellule.Offset(0, 1)
                Target.Offset(0, 2) = Cellule.Offset(0, 2)
                Exit For
            End If
        Next Cellule
End If
End Sub

 

[Iznogood1]

Re : Définir une plage issue de 2 blocs non contigus

Bonjour,

je te propose ce code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
    If Not Intersect([B:B], Target) Is Nothing Then
        Set r = Feuil2.[A:A].Find(What:=Target.Value)
        If r Is Nothing Then Set r = Feuil3.[A:A].Find(What:=Target.Value)
        If Not r Is Nothing Then
            Target.Range("B1:C1").Value = r.Range("B1:C1").Value
        End If
    End If
End Sub

A noter :

• il est dans "Worksheet_Change" plutôt que dans "Worksheet_SelectionChange"
  Permet une mise à jour même si, comme moi, l'utilisateur a désactivé le changement de cellule automatique après saisie dans les options d'Excel
• j'utilise Target.Range("B1:C1")... pour sélectionner 2 cellules en même temps
  Cette notation est parfois plus souple qu'OffSet
• j'utilise le "CodeName" des feuilles (Feuil2 au lieu de Sheets("Activités"), ce qui permet à ton code de fonctionner même si tu renommes ta feuille
• enfin, je ne saurais que trop te recommander d'utiliser
  
  Target.Range("B1:C1").Value = r.Range("B1:C1").Value
  
  au lieu d'un simple Target.Range("B1:C1")= r.Range("B1:C1")
  Evite de mélanger la cellule et la valeur de la cellule ; ça t'épargneras des soucis le jour où tu voudras manipuler la cellule elle-même, pas sa valeur

 

[Sebast]

Re : Définir une plage issue de 2 blocs non contigus

Bonsoir Iznogood1,

merci beaucoup pour ton aide et tes remarques très instructives.
Concernant le Worksheet_Change" plutôt que le Worksheet_SelectionChange, tu as raison, et c'était une faute de frappe de ma part (en fait, dans le menu déroulant, ça propose et j'ai validé trop vite ...)

pour la remarque avec .value, tu as raison, c'est plus lisible

pour le codename des feuilles, justement je préfère garder le nom littéral, pour moi plus parlant

Quant à l'ensemble, ça fonctionne impeccable, certains points me sont nouveaux mais je vais décortiquer tout ça !

Un grand merci

 

[Sebast]

Re : Définir une plage issue de 2 blocs non contigus

Rebonsoir Iznogood1,

Je viens de me pencher en détail sur le code, que je comprends maintenant mieux mais il me reste un point à éclaircir :

Target.Range("B1:C1").Value = r.Range("B1:C1").Value

Je ne comprends pas trop pourquoi dans le deuxième membre de l’égalité on a « r » devant range("B1:C1").Value

Est-ce une sorte de factorisation (un peu comme avec with/end with) ?
A quoi sert le r placé ici ?

Sinon, tes explications étaient limpides et m’ont convaincu d'adopter tes conseils

Encore merci et bonne soirée

 

[mapomme]

Re : Définir une plage issue de 2 blocs non contigus

Bonsoir Sebast, Iznogood1,

Pour le fun, pourquoi pas des formules dans le tableau de la feuille "Saisie" ?

en C2( (Unité), à recopier vers le bas (par la suite, un ajout de ligne via la touche <Tab>ou saisie directe sous le tableau recopiera la formule automatiquement) :

=SIERREUR(RECHERCHEV([@Rubrique];Tableau2[#Tout];2;0);"") & SIERREUR(RECHERCHEV([@Rubrique];Tableau1[#Tout];2;0);"")

et idem en D2 (Prix) :

=SIERREUR(RECHERCHEV([@Rubrique];Tableau2[#Tout];3;0);"") & SIERREUR(RECHERCHEV([@Rubrique];Tableau1[#Tout];3;0);"")

 

[Iznogood1]

Re : Définir une plage issue de 2 blocs non contigus

Bien vue MaPomme

@Sebast

Dim r As Range -> je déclare une variable r devant contenir une cellule (ou une référence à une cellule si tu préfères)
...
Set r = Feuil2.[A:A].Find(What:=Target.Value) -> j'affecte à cette variable "r" la cellule éventuellement trouvée par l'instruction Find

If r Is Nothing -> Si aucune cellule n'a été trouvée, alors la variable "r" contient "Nothing"
...
Enfin, si une cellule a été trouvée, donc stockée dans la variable "r", j'affecte les valeurs des 2 cellules adjacentes à celle "représentée" par "r" à celles adjacentes à la cellule "target"
D'où le code
Target.Range("B1:C1").Value = r.Range("B1:C1").Value

 

[Sebast]

Re : Définir une plage issue de 2 blocs non contigus

Bpnjour MaPomme, bonjpour iznoggod1,


Mapomme : merci pour cette solution, qui fait le job et m'est plus familière. Recherchev() fait partie de ce que je comprends (encore) aisément. Mais par sécurité, je préfère avoir le code en arrière-plan : moins de risque de corrompre la formule, voire de l'écraser ...

Iznogood1 : super, j'ai maintenant pigé le pourquoi du comment ! c'est important pour moi car le fichier exemple posté est ultra-épuré, je dois m'en inspirer pour adapter l'original qui lui contient beaucoup d'autres colonnes.

Encore merci à vous pour votre aide et vos explications précieuses