XL 2013 VBA - saisir ou copier une formule sur la colonne d'une ligne active

[Chrystel01]

Bonjour,

J'ai une base de donnée avec de nombreuses lignes.
Je voudrais que lorsque l'utilisateur saisit un code tiers sur la colonne D par exemple de chaque onglet (activité, dépenses, recettes), une macro permette de saisir des formules sur les colonnes B & C de cette même ligne ou copier les valeurs qui sont dans l'onglet formules (dans la version originale, ce sont des formules). La macro doit compléter cette seule ligne et pas toutes celles qui ont un code tiers en D.
Je suis débutante en VBA et peine à trouver une solution...
J'espère que vous pourrez m'aider et vous remercie par avance pour votre aide

Chrystel

 

[sylvanu]

Bonjour Chrystel,
Inutile de recourir au VBA.
Vous transformer votre zone en tableau ( Insertion/Tableau)
Dans ce cas, vous disposez d'un tableau structuré ... qui recopie automatiquement les formules.
Dans la PJ jointe, vous cliquez sur la cellule A7, première cellule vide, et vous entrez 6 par ex.
La tableau va s'agrandir et vous pouvez vérifier qu'en B7 C7 qu'il a recopié automatiquement les formules.

 

[Chrystel01]

Bonjour Sylvanu,

Je n'ai pas été assez claire, excusez moi. J'ai envoyé un cas simple pour expliquer mon pb mais le fichier est complexe...
ET je suis obligée de recourir au VBA car les formules que je vais saisir en vba ou copier de l'onglet formules font référence à de multiples des fichiers externes 1 fichier activité et 1 fichier balance par tiers saisi (200 tiers *2...)
Quand l'utilisateur aura complété la cellule D (tiers), je vais luis demander d'y rester pour que la macro complète les formules qui font le lien sur le fichier externe puis fasse un collage valeur pour éviter 200 *2 liaisons...)

Merci de votre aide

 

[sylvanu]

Re,
Désolé d'avoir été à coté de la plaque.
Un autre essai. J'utilise Worksheet_Change. Quand on remplit une cellule de la colonne D, alors la macro remplit automatiquement les cellules A B C avec des valeurs issues de formules. Les cellules ne contiennet que des valeurs.
Bien évidemment, seule la structure de la macro est à utiliser, les formules doivent être ré écrites.

Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 4 Then   ' Détection clic sur colonne D
        ' Valeur colonne A
        Cells(Target.Row, 1) = Cells(Target.Row - 1, 1) + 1         ' A modifier
        ' Valeur colonne B
        FormuleB = 5 * Target.Row                                   ' Formule colonne B à modifier
        Cells(Target.Row, 2) = FormuleB
        ' Valeur colonne C
        FormuleC = 7 * Target.Row                                   ' Formule colonne C à modifier
        Cells(Target.Row, 3) = FormuleC
    End If
End Sub

Cette macro dans être obligatoirement dans la feuille concernée.
En espérant avoir éviter un bis repetita.

 

[Chrystel01]

Re.

Merci pour votre rapidité et vos explications !
J'essaie de comprendre pour adapter ce cas à mon fichier mais je débute en vba et ce n'est pas très facile...
Je ne comprends pas comment fonctionne le fichier test car je ne vois pas la macro ?
If Target.Count > 1 Then Exit Sub Cela signifie quoi ?
If Target.Column = 4 Je suppose que c'est le n° de colonne
Les formules, je pourrai les remplacer par une formule qui va chercher les données d'un autre fichier ouvert grâce à la fonction indirecte que vous m'avez donné hier ?
du type =INDIRECT("'["&$D2&".xls]Dossier'!$A$1") => en saissant la lettre A, la macro ramène toutes les infos souhaitées du fichier A.

Merci encore ... et j'espère y arriver ! Vous me sauveriez la vie

 

[sylvanu]

Re,
1- Lisez tout :

Cette macro dans être obligatoirement dans la feuille concernée.

Ce genre de macro est dite événementielle car elle se déclenche sur un événement ( clic cellule, changement de valeurs ... ) et cette macro doit être dans la feuille concernée.
2- If Target.Count > 1 : Simple sécurité si l'utilisateur ne clique pas sur une cellule mais sur une plage.
3- Quand on clique sur une cellule, celle ci est nommée Target dans la marco.
On accède alors à certains paramètres :
Target.Column N° de colonne cliquée
Target.Row N° de ligne cliquée ...
4 est donc la colonne D
4- C'est justement pour ça que j'ai précisé : FormuleB= et FormuleC= pour être explicite.
dans ces deux formules vous mettez ce que vous voulez.
5- Y compris INDIRECT, sauf qu'il faut traduire l'action en VBA car en VBA cela n'a aucun sens.

 

[Chrystel01]

Merci Sylvanu

J'avais bien compris qu'elle était dans la feuille mais en passant par "Affichage macro - affiche les macros" je ne la voyais pas. Maintenant j'y accède.
Je vais appliquer toutes vos bonnes suggestions sur mes fichiers.
Traduire la fonction en VBA, c'est remplacer INDIRECT("'["&$D2&".xls]Dossier'!$A$1") par INDIRECT("'["&Cells(Target.Row, 4) &".xls]Dossier'!$A$1") ?

Merci beaucoup !

 

[sylvanu]

Non, il suffit de faire :

NomFichier= Cells(Target.Row, 4)   & ".xls"                ' Construction nom du fichier'
NomFeuille="Dossier"                        ' Nom de la feuille source'
Valeur=Workbooks(NomFichier).Sheets(NomFeuille).Range("A1")        ' On lit la valeur'
Range("B4")=Valeur                            ' On range la valeur'

 

[Chrystel01]

Rebonjour,

Je suis vraiment désolée, je ne suis pas familière avec VBA et je peine à assembler le tout.. J'ai fait différents tests, toujours avec une erreur à l'exécution de la macro.
Je vous joints un ex et les 2 fichiers
Pourriez-vous svp m'aider encore une fois ?

Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 4 Then ' Détection clic sur colonne D
NomFichier = Cells(Target.Row, 4) & ".xlsx" ' Construction nom du fichier'
NomFeuille = "Dossier" ' Nom de la feuille source'

valeur = Workbooks(NomFichier).Sheets(NomFeuille).Range("A1") ' On lit la valeur'
Cells(Target.Row, 1) = valeur
End If
End Sub

Sachant que je vais aller chercher beaucoup d'informations, je peux consolider les 2 dernières lignes avant le end if ? J'ai essayé sur le modèle suivant mais ça ne fonctionne pas....
Cells(Target.Row, 1) = Workbooks(NomFichier).Sheets(NomFeuille).Range("A1")

Merci de votre aide. Après je l'appliquerai sur la 50aine de champs à rappatrier...

 

[sylvanu]

Bonjour, quelles infos doit on rapatrier lorsqu'on entre une lettre dans la colonne D ?

 

[sylvanu]

Voir un test en PJ.
Comme je suppose qu'il y a plein d'infos à rapatrier, faites donc deux fichiers tests (anonymes) avec la bonne structure. avec de bonnes explications, je vous rapatrierais les infos.

 

[Chrystel01]

1- Je commence à alimenter mon support

1 fichier CONSOTEST avec 3 onglets :
-recettes et dépenses qui ont pour fichier source « source test »
- l’activité qui aura pour source un autre fichier

Sur le fichier CONSOtest, onglet « Recettes_réalisées3 je saisis en colonne AD le nom du fichier
Les colonnes C à R vont alors se compléter automatiquement

Exemple du champ
« PSU » (colonne D) : je vais chercher dans Sourcetest la cellules D96
« Part usagers » (colonne C), il me faudrait regrouper les cellules D97 et D98 de SourceTest

Pour l’instant j’ai écrit cela dans le code

Cells(Target.Row, 3) = Workbooks(NomFichier).Sheets(NomFeuille).Range("D97")

Et en commentaire l’addition ' +Cells(Target.Row, 3) = Workbooks(NomFichier).Sheets(NomFeuille).Range("D98"))

Comment peut-on mettre dans une cellule le regroupement de plusieurs autres ?

Je vais essayer de rapatrier les formules manuellement même si cela est laborieux car le rendu est urgent et je ne maîtrise pas assez. D’autre part, dans le fichier activité les informations sont éparses.


2 – Je me suis aperçue que quand on clique sur la cellule, la macro ne fonctionne pas systématiquement (rien ne se passe ) ce qui est problématique.
Je ne sais pas quels sont les cas où on peut avoir cette anomalie, mais j’ai vu que cela survient quand on a par exemple saisi un nom de fichier erroné

• La macro s’interrompt avec un message d’erreur : erreur d’exécution 9 - l’indice n’appartient pas à la sélection »

Donc je clique sur fin et corrige le fichier source mais après la macro ne fonctionne plus (j’avais eu le même cas sur votre fichier).
Il faut refermer tous les fichiers et les rouvrir pour que cela fonctionne

Savez-vous comment faire pour essayer de résoudre cela ? que la macro ne plante pas et n’empêche pas les prochaines révisions du nom saisi

Je vous joints la macro que je personnalise si vous pouviez empêcher ce bug ou permettre les regroupements

Un grand merci pour le temps que vous m’accordez. Votre aide est très précieuse !

 

[sylvanu]

Bonjour,
désolé pour ce blocage, je n'avais pas prévu cette erreur.
C'est corrigé en PJ.
Pour le fichier post #12, je ne comprends pas certaines choses :

« PSU » (colonne D) : je vais chercher dans Sourcetest la cellules D96
« Part usagers » (colonne C), il me faudrait regrouper les cellules D97 et D98 de SourceTest

Comment calcule t-on les N° de lignes qu'on doit rapatrier ?

 

[Chrystel01]

Bonjour Sylvanu,

Merci pour l'ajout de la fonctionnalité qui corrige les erreurs ! Cela me rend grandement service.

Les N° de lignes à rapatrier, on les voit grâce à la colonne observation du fichier source test sur lequel j'indique le nom du champ du fichier Consotest sur lequel le montant va être copié :
* "PSU » (colonne D) : je vais chercher dans Sourcetest la cellules D95
* « Part usagers » (colonne C), il me faudrait regrouper les cellules D97 et D98 de SourceTest
*Autres subventions" (colonne J) : il me faudrait grouper les cellules 101 à 103 +105 + 107 à 109
Je voulais savoir par ex sur le champ "autres subventions" comment rapatrier non pas une cellule mais l'addition de ces cellules ?
Je vais gérer toutes les lignes de copies car elles concernent de multiples champs éparses sur 3 onglets...et vous m'avez déjà énormément apporté. VBA est vraiment très puissant. Ca mérite de s'y investir...
Merci encore pour toute votre précieuse aide !

 

[sylvanu]

Re,
Désolé je ne comprends pas ce que veut dire "regrouper".
D97 D98 sont des nombres, vous voulez remontez 1002/1003 ou la somme 2005 ?
Même question pour Autres subventions, vous voulez la somme ou les chaines de caractères ?