1 Colonne pour plusieurs Autofill

Le 03/03/2022 à 11:36

Bonjour,

Mon problème est quasiment dans le titre puisque je recherche le moyen d'utiliser Autofill dans une colonne mais plusieurs fois.

Pour donner un peu plus de contexte :

  • les colonnes sont fixes mais le nombre de ligne varie
  • les positions des bâtiments (à l'intérieur de la colonne) varient, et celles des pièces (dans une seconde colonne) varient aussi.
  • Le nom des bâtiments et des pièces varient trop. On ne peut pas se baser sur le contenu de la cellule.

Voici une capture d'écran du fichier que je joins également. On peut y trouver le résultat à droite et la situation initiale à gauche.

On peut se baser sur la colonne B (nom prénom) pour la dernière ligne.

En vous remerciant,

NB : les noms/prénoms sont factices, et les couleurs ne sont là que pour montrer le résultat et donc ne sont pas nécessaires.

Remarque : je viens de me rendre compte d'un problème sur mon fichier.
Comme on peut le voir en D24, l'autofill poursuite sa course jusqu'à la pièce A.
Je vais ajouter des tirets sous forme de texte : "---" dans la colonne pièce dès lors que la case bâtiment sera remplie (AVANT l'autofill que je recherche).

De cette manière, MICHEL Francis (G26) aura un série de tiret dans la case I26.

autofill adressage
14test-autofill.xlsm (18.60 Ko)
Le 05/03/2022 à 09:30

Bonjour

Voila qui, je crois, résout ton problème

15test-autofill.xlsm (21.86 Ko)
Le 08/03/2022 à 12:17

Bonjour Yal,

Je te remercie pour ton aide. J'ai naturellement pris le temps de la décortiquer pour mieux la comprendre.

Aussi, j'aurais quelques questions si tu es d'accord.

  • Est-ce que [L1] est strictement équivalent à Range("L1") ou à Cells(1,12) ?
  • Comment puis-je appliquer les résultats dans les mêmes colonnes (C et D) ?

Je tente ceci : Columns("C:D") = Columns("M:N")

Code que je rajoute à la suite du vôtre. Est-ce que ça vous parait bon ?

En vous remerciant,

Le 08/03/2022 à 12:51

Bonjour,
Une proposition Power Query.
Pas de VBA.
Cdlt.

9test-autofill.xlsx (19.89 Ko)
Le 08/03/2022 à 12:59

Bonjour

Les trois sont effectivement équivalentes. J'ai mis les résultats en L1 pour ne pas modifier vos plages afin que vous puissiez comparer mais si les résultats doivent remplacer les données d'origine le plus simple est de faire comme ceci :

Sheets("Autofill").Range("B1").Resize(UBound(tb1), 3) = tb1
Le 08/03/2022 à 16:01

Bonjour Jean-Eric,

Effectivement, peut-être que l'usage de PowerQuery serait plus simple.

Je ne l'ai jamais utilisé et je le ferai probablement plus tard. Du coup, est-ce que d'après votre fichier, il me serait possible de voir les manipulations que vous avez fait ?

_____

Yal,

Je vous remercie.

Pourquoi utilisez-vous Resize ? Jusque là on avait tb1 = Range("B1:D" & dl).Value.

Autre question qui m'avait échappée sur la première partie du code. Les deux fonctions If sont imbriquées. J'aurais eu tendance à les mettre l'une à la suite de l'autre. Est-ce qu'il n'y a pas un risque de ne pas atteindre la seconde fonction If si la condition de la première n'est pas remplie ?

Merci beaucoup !

Le 08/03/2022 à 16:18

Pourquoi utilisez-vous Resize ? Jusque là on avait tb1 = Range("B1:D" & dl).Value

La première version était un peu "bourin" la deuxième est plus pertinente.

Remarque : je viens de me rendre compte d'un problème sur mon fichier.

Comme on peut le voir en D24, l'autofill poursuite sa course jusqu'à la pièce A.

Je vais ajouter des tirets sous forme de texte : "---" dans la colonne pièce dès lors que la case bâtiment sera remplie (AVANT l'autofill que je recherche).

C'est le résultat des if successifs.

Est-ce qu'il n'y a pas un risque de ne pas atteindre la seconde fonction If si la condition de la première n'est pas remplie ?

C'est justement le but recherché.

Le 08/03/2022 à 16:49

Effectivement. J'avais perdu de vue mes besoins en essayant de comprendre les enchaînements. C'est top !

Si j'ai déjà des variables "i" en amont ou en aval de cette partie du code, dois-je prendre des précautions. En l'occurrence il s'agit du même type d'usage (i représente une variable liée à des lignes).

J'aurais tendance à dire que non.

edit : est-ce que le fait d'avoir Option Base 1 modifie quelque chose au code ?

C'est lié à une autre partie du code. Il me semble que c'est pour donner le "repère de départ".

Le 08/03/2022 à 18:17

Pour le nom des variables. Inutile de multiplier le nombre de variables qui ne servirait qu'une seule fois. Il faut juste prendre garde à la façon dont on les gère.

For i = 1 to 10
    blablabla
next i
'ça marche
for i = 1 to 10
   blablabla
   for i i = 1 to 20
       reblabla
   next i
next i
là y a un problème!

Option Base 1 ne change rien à la logique ou la syntaxe du code. Il faut juste savoir si les tableaux commencent à zéro ou à un
Puisqu'on en est aux options je ne saurais trop recommander l'usage d' Option Explicit qui oblige à déclarer les variables avant de les utiliser.

Le 08/03/2022 à 19:46

Bonsoir,
Quelques explications pour reproduire la chose avec Power Query.
A te relire.
Cdlt.

13test-autofill.xlsx (21.37 Ko)

1 - Mettre les données sous forme de tableau structuré
2 - Renommer le tableau : t_données
3 - Ruban, Données, Récupérer et transformer des données, A partir d'une plage ou un tableau
4 - L'éditeur Power Query est ouvert
5 - Sélectionner les colonnes Bâtiment et Pièce (comme sous Excel, avec Ctrl)
Clic-droit, Remplir, Vers le bas
6 - Sélectionner la colonne NOM Prénom
Filtrer (comme sous Excel) en décochant null
7 - Ruban, Accueil, Fermer et charger, Fermer et Charger dans…
Opter pour Tableau et choisir la destination

Le 09/03/2022 à 09:45

Jean-Eric, je vous remercie.

Je le conserve précieusement quand je m'y mettrai. Je me demande même si des actions de PowerQuery sont intégrables dans des macros. Je sais qu'il s'agit d'autres choses qui permettent de simplifier ce que ferait des macros compliquées, mais ça éviterait d'avoir une macro A puis une automatisation power Query B, suivi d'une autre macro C.

Je laisse ça en suspend, encore merci !

Yal,

Le souci, c'est que j'assemble des bouts de code qui fonctionnent seuls. Le cœur du code a été écrit avec l'option base 1. Ce qui fait que le code restant dès qu'il implique des tableaux, ne fonctionne pas en l'état.

Je peux éventuellement ouvrir un autre sujet mais j'avoue ne pas savoir quoi modifier. Pour moi il s'agit juste d'un décalage de 1 à respecter ça-et-là quand j'utilise des tableaux...

Le 09/03/2022 à 12:15

Pour moi il s'agit juste d'un décalage de 1 à respecter ça-et-là quand j'utilise des tableaux...

C'est tout à fait cela. En exécutant le code en pas à pas vous pourrez repérer les formats des tableaux en mémoire en adapter vos boucles au cas par cas

Le 09/03/2022 à 15:24

Merci

Mais.. comment voir les tableaux en mémoire ? (Je ne trouve pas).

J'utilise f8 pour la méthode pas à pas.

Mais il vient un moment où il exécute la ligne qui décale et je ne parviens pas à identifier pourquoi il colle le tableau à tel ou tel endroit. Je pense aussi que ça n'est pas sans lien avec ma variable dernière ligne.

Est-ce que ces codes sont parfaitement similaires ?

Dim dl as variant

dl  = Cells(Rows.Count, 2).End(xlUp).Row

For i = 2 To Sh.Range("C" & dl) 'Ecriture 1

For i = 2 To Sh.Range("C" & Rows.Count).End(xlUp).Row 'Ecriture 2

Entre ça et les tableaux, je perds mes repères et je n'arrive pas à me raccrocher à quelque chose de solide.

Le 09/03/2022 à 15:37

Non il ne sont pas équivalents

dl  = Cells(Rows.Count, 2).End(xlUp).Row

cherche la dernière cellule non vide de la colonne 2 donc B

Et le reste est à l'avenant

Je vous ai fourni un code qui marche pourquoi ne l'utilisez vous pas?

Le 09/03/2022 à 16:01

Je l'utilise et j'essayais même d'adapter une autre partie du code avec le vôtre.

En gros, je cherche à faire un tri avec une liste de mots-clefs. Mais je n'ai pas bien saisi le report de mes résultats (injection de mes tableaux dans les colonnes prévues)

Voici un exemple de travail : vous y trouverez l'ensemble du code. En gros, le cœur de la macro recherche les mots dans une feuille "mots-clefs", et ajoute un 1 en face s'il trouve une correspondance.

Puis en théorie un filtre avec les lignes retenues devrait s'appliquer. Mais dès qu'il est question de tableau, ça marche pas bien.

709032022.xlsm (34.11 Ko)
Le 09/03/2022 à 17:49

Bon ben si vous aviez commencé en envoyant ce fichier on aurait gagné du temps. Vous avez tendance à vous embrouiller dans le comptage des colonnes.
Voici une version qui, je crois correspond à vos attentes. Lancez la macro avec F5 pour voir le résultat

2109032022-yal-v1.xlsm (37.75 Ko)
Le 09/03/2022 à 18:32

C'est vrai. Je n'ai pas osé.

Alors c'est quasiment bon (et mille fois merci déjà !).

Par contre je n'arrive pas à reporter "vers le bas" comme ceci :

autofillvba

Les lignes de départ sont en jaune.

J'ai l'impression que ça "remonte".

autofillvba2

Lorsque j'essaye de faire +1 au lieu de -1, il m'indique qu'il y a une référence en dehors du tableau. Je pense que c'est en bas.

Peut-être qu'il faut boucler de i=3 jusqu'à dl-1.

Comme ceci :

        'REMPLISSAGE AUTOMATIQUE============
        dl = Cells(Rows.Count, 3).End(xlUp).Row 'Renommer dernière ligne
        tb1 = Sh.Range("D1:E" & dl).Value 'Chargement des colonnes D,E et F
        For i = 3 To UBound(tb1) - 1 'Travail à partir de la ligne 3 jusqu'à la dernière ligne
            If tb1(i, 1) = "" Then 'Si la case "Adresse Bâtiment" est vide, alors :
                tb1(i, 1) = tb1(i + 1, 1) 'Copier la cellule "bâtiment" du dessus
                If tb1(i, 2) = "" Then 'Si la case "Adresse pièce" est vide, reporter le nom de la pièce du dessus
                  tb1(i, 2) = tb1(i + 1, 2)
                End If
            End If
        Next i
        Sh.Range("D2:E" & dl) = tb1
Le 09/03/2022 à 19:41

Il y a une erreur dans mon code. Il faut remplacer ces quelques lignes

        'REMPLISSAGE AUTOMATIQUE============
        dl = Cells(Rows.Count, 3).End(xlUp).Row 'Renommer dernière ligne
        tb1 = Sh.Range("D1:E" & dl).Value 'Chargement des colonnes D,E et F
        For i = 3 To UBound(tb1) 'Travail à partir de la ligne 3 jusqu'à la dernière ligne
            If tb1(i, 1) = "" Then 'Si la case "Adresse Bâtiment" est vide, alors :
                tb1(i, 1) = tb1(i - 1, 1) 'Copier la cellule "bâtiment" du dessus
                If tb1(i, 2) = "" Then 'Si la case "Adresse pièce" est vide, reporter le nom de la pièce du dessus
                  tb1(i, 2) = tb1(i - 1, 2)
                End If
            End If
        Next i
        Sh.Range("D1:E" & dl) = tb1

c'est à cette ligne qu'est l'erreur. Dans le code que je vous ai fourni Il y
Sh.Range("D2:E" & dl) = tb1
et c'est
Sh.Range("D1:E" & dl) = tb1
qu'il faut mettre puisqu'on a chargé
tb1 = Sh.Range("D1:E" & dl).Value

Le 09/03/2022 à 22:34

Merci !

Merci ! et merci !

(C'est top, je suis très content)

Rechercher des sujets similaires à "colonne autofill"