Résolu formule excel copie cellules decaler

[GTmacrodeb]

Bonjour à toute la communauté,

Je vous sollicite car je bloque sur une formule qui me permettrait de copier les données d’un tableau sur une 2e feuille en fonction de 2 critères.

Pour présenter le « projet » :
J’ai en "feuille 1", un tableau avec en colonne A (Identité), en colonne F (Date) et en colonne I (Une case de validation « X » ou vide).
Je souhaite extraire/copier dans une "feuille 2", les identités des personnes pour lesquelles la date indiquée en colonne F "feuille 1" correspond à la date indiquée dans la cellule (F5) de la "feuille 2" (cellule figée) et pour les personnes dont la colonne I ("feuille 1") est validée avec le « X ».
J’ai créé une première formule que j’ai copiée à la première ligne de mon tableau "feuille 2" (cellule A12) puis étirée sur la hauteur du tableau.
=SI(ET($F$5='Tableau suivi'!$F$10:$F$34;'Tableau suivi'!$I$10:$I$34="X");'Tableau suivi'!$A$10:$A$34;"")

La formule copie bien les données en fonction des critères choisis cependant elle les copie à des positions que je ne comprends pas. Je pense qu’il y a une fonction DECALER à intégrer à cette formule mais je ne comprends pas bien le fonctionnement. Les cellules ne se copient pas dès la première ligne.

Je précise que les informations renseignées dans la "Feuille 1" sont renseignées au fur et à mesure, je n’ai donc aucune idée de la dernière ligne qui sera renseignée. J’ai donc pour le moment défini arbitrairement le tableau "Feuille 1" jusqu’à la ligne 34 dans la formule mais il serait préférable qu'elle soit applicable sans limite.

J’espère avoir suffisamment détaillé ma problématique pour avoir des conseils.

En vous remerciant par avance de votre aide.

GTmacrodeb

 

[drul]

Salut,
Pourquoi utilisé des données absolues ?
Je ferais ceci:
=SI(ET($F$5='Tableau suivi'!F10;'Tableau suivi'!I10="X");'Tableau suivi'!A10;"") que tu étireras ensuite aussi loin que nécessaire

 

[GTmacrodeb]

Bonjour Drul,

Tout d'abord merci d'avoir pris le temps de répondre à ma demande d'aide.

Je pense que j'ai mal exprimé mes difficultés.

Lorsque j'utilise la formule que tu me donnes (comme la mienne), Excel vérifie les conditions et me copie toutes les cellules dans la "Feuille 2" (si les conditions sont vérifiées, copie des valeurs/données concernées, si les conditions ne sont pas remplies cellule vide).

En fait, je souhaiterai qu'il ne me copie que les cellules dont les conditions sont vérifiées mais je ne parviens pas à l'inscrire sous forme de fonction.

Si je devais illustrer encore, je dirai que ma fonction ressemblerait à un filtre sur la "Feuille 1" avec vérification de la date (en colonne F) et de la "X" (en colonne I). Une fois ce filtre appliqué, je copie les lignes concernées (colonnes A/B/C) et je les colle dans la "Feuille 2" à partir de la cellule A12.

En espérant avoir été un peu plus précis dans mon exposé.

Merci encore en tout cas.

 

[drul]

En gros tu veux pas les trous ...
T'es allergique au VBA ? parce que la à part une macro je vois pas comment faire ...

 

[GTmacrodeb]

Pas le moins du monde mais mes compétences en ce domaine sont limitées...c'est pourquoi j'ai hésité à le mentionner à la fin de mon dernier message.

J'ai pourtant déjà travaillé sur quelques projets à une époque, déjà sur ce forum, avec un réputé "zeb" que je ne vois pas dans les parages.

Je veux bien me lancer dans la conception d'une macro mais je n'ai aucune idée par où commencer. Dois-je faire la manip avec les filtres et un enregistreur de macro ?

Merci.

 

[drul]

Non les filtres c'est pas la meilleures solutions ici.
Quelques questions d'ordre générale:
1) Tu penses lancé la macro à chaque changement sur feuille1 ou avec 1 bouton ?
2) Peut-on envisagé un effacement total des données dans la plage concerné de la feuille2 à chaque execution de a macro ? (je ne vois pas comment gérer cela de manière incrémentale).

P.S. zeb n'a malheureusement plus donné signe de vie depuis bien longtemps, j'ai un peu pris le relais avec mes modestes moyens...

 

[GTmacrodeb]

Ton "pseudo" m'est familier et quand je parle d'époque je ne vais pas dire que les - de 20 ans ne peuvent pas s'en souvenir mais cela remonte à 2012...

Je souhaite lancer cette fonction par bouton car cela me semble plus pertinent et en plus je dois conserver les données de la "Feuille 1".

A la vue des peu de sujets que j'ai parcourus, "tes modestes moyens" ont dépanné bien des personnes.

Merci encore.

 

[drul]

Ce soir je vais être court, on regarde ça demain.

 

[drul]

Ok, quels idée aurais-tu pour faire cette macro ?
Tu arrives à me faire un "code" en français ? (c'est une très bonne base)

N.B. le but est que tu progresse dans l'utilisation du VBA, pas que JE fasse tes macros, j'espère que cela te convient.
N.B.2 cela peut prendre un peu de temps pour arriver au bout ... (même si ici c'est assez simple)

 

[GTmacrodeb]

Juste pour préciser un peu l'organisation de ma "Feuille 2", il s'agit d'une feuille type dans laquelle je ne peux pas insérer des lignes.

La "Feuille 2" s'étend de la ligne A1:J39. Les données de la "Feuille 1" à extraire sous conditions sont située sur les colonnes A, B et D (à partir de la ligne 10). Ces données sont ensuite à copier sur la "Feuille 2" dans 3 colonnes qui sont réparties de A à H (colonnes fusionnées ABC / DEF / GH) et qui s'étend des lignes 12 à 28.

Comment peut-on gérer les quelques possibles rares cas où j'aurai plus de 17 lignes à copier de la "Feuille 1" ?

Je suis à ton écoute pour apprendre à traiter ce sujet.

Merci encore.

 

[GTmacrodeb]

Par rapport à ma formule initiale, le point qui me bloque est le fait de ne copier que les cellules concernées par la condition, la formule recopiant les lignes dont les conditions sont remplies et laissant des lignes vides pour les autres, d'où mon idée de départ en intégrant une fonction DECALER, même si je ne suis pas certain que cela soit adapté.

NB : Je rédige mon code en français et reviens vers toi.

 

[drul]

Si tu as plus de 17 lignes à traiter, on peut:
1) Faire une msgbox d'erreur
2) tronquer et ne copier que les 17 premières values
3) faire un mix des 2
4) Copier les données supplémentaire ailleurs ...
5) Prévoir une zone plus grande dans feuille2 (des fois les solutions les plus simples sont les meilleures )

J'attends ton "code"
A ma connaissance pas de moyen de "déplacer" des données à partir de formule :/ (on arriverait peut-être à qqch à partir de la fonction INDIRECT, mais c'est quand même très compliqué ...)

 

[GTmacrodeb]

Ci-dessous, une première ébauche de "mon" code en français :

Dans la colonne F de ma "Feuille 1", je cherche/sélectionne les lignes qui ont la même valeur (je précise que c’est une date de la forme JJ/MM/AAAA) que ma cellule F5 ("Feuille 2"). --> il s’agit d’une cellule fusionnée sur FG à cet endroit, je ne sais pas si cela a une importance.

Dans ces lignes, je vérifie et sélectionne uniquement les lignes pour lesquelles la colonne I est égale à « X » ("Feuille 1"),

Pour ces lignes, je copie les colonnes (A, B et D) de la "Feuille 1" et je les colle respectivement dans les colonnes A, D et G de ma "Feuille 2" à partir de la ligne 11 (voir mon message précédent sur l’organisation de la feuille 2).

Pour la partie suivante je ne sais pas si cela est possible :
Si le nombre de lignes concernées est supérieur à 17, je copie complètement la feuille 2 et la colle en dessous. La copie des données suivantes serait donc réalisée à partir de la ligne 51.

EDIT1 : pour la "Feuille 2", il s'agit d'une feuille d'émargement, donc il me faut suffisamment de hauteur sur ces lignes pour permettre des signatures.

 

[drul]

Un premier jet (non tester)

Sub ChercheEtCopie()
    Dim cell As Range
    Dim target As Range

    Sheets(2).Range("A12:H28").ClearContents 'on efface toutes les données qu'il y avait en feuille 2 dans la zone concernée

    Set target = Sheets(2).Range("A12") 'définit la première case cible, N.B. 11 ou 12 ? il y a une incoherence dans tes messages ...
    
    For Each cell In Sheets(1).Range("A1:A39") ' Pour toutes cellule de la plage A1:A39
        If cell.Offset(0, 5).Value = Sheets(2).Range("F5").Value Then ' on regarde si la date correspond
            If cell.Offset(0, 8).Value = "X" Then ' on regarde si il y a X en colonne I, attention case sensitive !
                target.Value = cell.Value 'on copie "A" dans "ABC"
                target.Offset(0, 1).Value = cell.Offset(0, 5).Value 'on copie "F" dans DEF
                target.Offset(0, 4).Value = cell.Offset(0, 8).Value 'on copie "I" dans GH
                Set target = target.Offset(1, 0) ' on pointe sur la ligne suivante
                If target.Row > 28 Then 'si il y a plus de 17 données à copier
                    MsgBox "trop de donnée à copier"
                    End ' on s'arrête là. (on verra plus tard pour le reste)
                End If
            End If
        End If
    Next
End Sub

Test et dis moi ce que ça donne.

Edit: corrigé une ou deux erreurs ...

 

[GTmacrodeb]

Ci-dessous le code que j'ai modifié avec mes commentaires :

Sub ChercheEtCopie()
    
    Dim cell As Range
    Dim target As Range
 
    ActiveSheet.Range("A12:H28").ClearContents 'on efface tout ce qu'il y avait en feuille 2 dans la zone concernée
    'N.B. si tu veux garder le format de cette plage utilise ClearContents au lieu de Clear --> Je suis passé en ActiveSheet car je souhaite que les personnes réalise dans la mesure du possible une copie de cette feuille (manuellement). Cela n'altère pas le fonctionnement de la macro et si oubli la macro est tout de même adaptée.
 
    Set target = ActiveSheet.Range("A12") 'définit la première case cible --> il s'agit bien de cette cellule
    
    For Each cell In Sheets("Tableau suivi").Range("A10:A39") ' Pour toutes cellule de la plage A1:A39 --> Le tableau risque d'excéder cette plage, je ne souhaiterai pas de limite maximale
        If cell.Offset(0, 5).Value = ActiveSheet.Range("F5").Value Then ' on regarde si la date correspond
            If cell.Offset(0, 8).Value = "X" Then ' on regarde si il y a X en colonne I, attention case sensitive !
                target.Value = cell.Value 'on copie "A" dans "ABC"
                target.Offset(0, 1).Value = cell.Offset(0, 1).Value 'on copie "F" dans DEF --> on copie "B" dans DEF
                target.Offset(0, 4).Value = cell.Offset(0, 3).Value 'on copie "I" dans GH --> on copie "D" dans GH
                Set target = target.Offset(1, 0) ' on pointe sur la ligne suivante
                If target.Row > 29 Then 'si il y a plus de 17 données à copier
                    MsgBox "trop de donnée à copier"
                    End ' on s'arrête là. (on verra plus tard pour le reste)
                End If
            End If
        End If
    Next
End Sub

En l'état ce code fonctionne très bien pour une copie de lignes dont le nombre maximal est 17.

Merci beaucoup pour cette 1ère étape.

EDIT1 : je ne parviens pas à retrouver la balise de langage à utiliser pour le code, peux-tu m'éclairer ?

 

[drul]

la balise c'est code="vb"

Le tableau risque d'excéder cette plage, je ne souhaiterai pas de limite maximale:

Ok, on peut determiné la dernière cellule utilisé en colonne A (y a t'il forcément une valeur en A pour chaque valeur en F et I ?)
Pour ce faire, on peut procéder de la manière suivante:

For Each cell In Sheets("Tableau suivi").Range(Sheets("Tableau suivi").Range("A10"), Sheets("Tableau suivi").Cells(Rows.count,"A").End(xlUp))

Remarque:
If target.Row > 29 Then, en fait c'est > 28 ...

 

[GTmacrodeb]

Je te confirme qu'il y aura forcément une valeur en A si les colonnes F et I sont renseignées.

Bien noté pour la ligne 28.

Une idée pour la suite du code si le nombre de lignes excède 17, il est peut-être possible de faire une copie de la "Feuille 2" dans un nouvel onglet en la renommant "Feuille 2" (1), à la manière de ce que fait Excel de façon automatique lorsque l'on copie un onglet.

Et demander à la macro d'effacer les données comprises sur la zone (A12:H28) comme cela est le cas dans le début du code et de reprendre la copie des lignes suivantes.

Cette possibilité est-elle plus simple dans la réalisation que la recopie du tableau en bas de feuille ?

A mon avis, le nombre de lignes concernées par les conditions ne devrait pas dépasser 50 (à chaque fois) donc on ne devrait pas avoir besoin de créer plus de 4 feuilles.

 

[drul]

Alors essaye la formule ci-dessus

 

[drul]

C'est assez simple en effet. une idée de comment procéder ?

 

[GTmacrodeb]

Ci-dessous, une 1ère ébauche de code (qui ne fonctionne pas) : :/

Sub ChercheEtCopie()
    
    Dim cell As Range
    Dim target As Range
 
    ActiveSheet.Range("A12:H28").ClearContents 'on efface tout ce qu'il y avait en feuille 2 dans la zone concernée
    'N.B. si tu veux garder le format de cette plage utilise ClearContents au lieu de Clear
    
    Set target = ActiveSheet.Range("A12") 'définit la première case cible
    
    For Each cell In Sheets("Tableau suivi").Range(Sheets("Tableau suivi").Range("A10"), Sheets("Tableau suivi").Cells(Rows.Count, "A").End(xlUp))
        If cell.Offset(0, 5).Value = ActiveSheet.Range("F5").Value Then ' on regarde si la date correspond
            If cell.Offset(0, 8).Value = "X" Then ' on regarde si il y a X en colonne I, attention case sensitive !
                target.Value = cell.Value 'on copie "A" dans "ABC"
                target.Offset(0, 1).Value = cell.Offset(0, 1).Value 'on copie "F" dans DEF --> on copie "B" dans DEF
                target.Offset(0, 4).Value = cell.Offset(0, 3).Value 'on copie "I" dans GH --> on copie "D" dans GH
                Set target = target.Offset(1, 0) ' on pointe sur la ligne suivante
                If target.Row > 28 Then 'si il y a plus de 17 données à copier
                        ActiveSheet.Select
                        ActiveSheet.Copy Before:=Sheets(10) 'comment indiquer que l'on souhaite copier à la suite de la feuille active ?
                        ActiveSheet.Range("A12:H28").ClearContents 'comment indiquer la nouvelle feuille créée ?
                            For target.Row > 29 Then 'je ne sais pas comment l'écrire sous forme de code
                                 Set target = ActiveSheet.Range("A12")
                                    If cell.Offset(0, 5).Value = ActiveSheet.Range("F5").Value Then ' on regarde si la date correspond
                                    If cell.Offset(0, 8).Value = "X" Then ' on regarde si il y a X en colonne I, attention case sensitive !
                                        target.Value = cell.Value 'on copie "A" dans "ABC"
                                        target.Offset(0, 1).Value = cell.Offset(0, 1).Value 'on copie "F" dans DEF --> on copie "B" dans DEF
                                        target.Offset(0, 4).Value = cell.Offset(0, 3).Value 'on copie "I" dans GH --> on copie "D" dans GH
                                        Set target = target.Offset(1, 0) ' on pointe sur la ligne suivante
                                    If target.Row > 28 Then
                       End ' on s'arrête là. (on verra plus tard pour le reste)
                End If
            End If
        End If
    Next
End Sub

J'ai oublié de te poser une question sur ton code, qu'entends-tu pas case sensitive ?