VBA Excel sélectionner la première colonne vide Résolu

Question

Bonjour,

Une nouvelle fois je viens faire appel à votre aide. J'ai créé grâce à l'enregistreur automatique une macro pour écrire sur la première colonne vide de la ligne 1
J'ai du coup sélectionné la colonne C1 et j'ai fait "ctrl + flèche droite" et encore une fois flèche à droite.

 Range("C1").Select
    Selection.End(xlToRight).Select
    Range("R1").Select 

Seulement voilà à la lecture de mon code il m'écrit sélection de la dernière cellule donc jusque là pas de problème, mais après il me sélectionne la cellule R1 qui est sûrement ma 1ère colonne vide, mais d'une semaine sur l'autre la sélection ne sera pas forcément la cellule R1 mais peut être S1 ou T1 ou U1 ou AA1 etc.

Sauriez vous comment réaliser ce code ?

Encore une fois merci pour votre aide,

Amicalement
Jérôme

Configuration: Windows / Chrome 103.0.0.0

via55 · Answer

Bonsoir Jérôme,

y= Range("C1").End(xlToRight).Column 
Cells(1,y).Select

Cdlmnt
Via

via55 · Answer

Bonjour,

Au plus simple il suffit de rajouter  1 dans la 1ere ligne de code ☺
y= Range("C1").End(xlToRight).Column +1

Cdlmnt
Via

via55 · Answer

Bonsoir

C'est vrai que ce n'est pas clair
Pourquoi parle tu de rajouter le code à chaque fois  ?
S'il est en début de macro, à chaque fois que tu lances la macro la dernière colonne vide  est déterminée par y= Range("C1").End(xlToRight).Column +1
Ensuite tu sélectionnes cette cellule avec Cells(1,y).Select 
Tu peux écrire dedans  : Cells(1,y) = "texte à écrire" 
Si tu veux dans la même macro écrire ensuite dans la cellule vide à droite de cette dernière pas la peine de faire rechercher à nouveau la dernière colonne vide il suffit de prendre la cellule Cells(1,y+1)

Quand au 2eme point ta dernière ligende code doit être
Selection.AutoFill Destination:=Range(Cells(1, y), Cells(derligne, y))

Cdlmnt
Via

via55 · Answer

Bonjour

Après l'instruction Cells(1, y + 2) = "texte à écrire 3" on est positionné en colonne y+2  donc   Selection.AutoFill Destination:=Range(Cells(1, y), Cells(derligne, y)) renvoie une erreur puisque tu indiques dans cette 2eme instruction y comme colonne de départ

Soit tu veux effectivement remplir la colonne y et il faut d'abord revenir sur cette colonne avec un Select.Cells(1,y) soit tu veux remplir la colonne y +2 et il faut coder l'AutoFill en conséquence Selection.AutoFill Destination:=Range(Cells(1, y)+2, Cells(derligne, y+2))

Cdlmnt

Via

via55 · Answer

Re

Les références en RC se font par rapport à la cellule où est entrée la formule

Tu dois te situer à priori dans la colonne R donc N est la 4éme colonne avant (-4) , M est la 5ème avant (-5) et T est la 2ème après (2)

Mais tu peux utiliser les références habituelles aussi en VBA comme ceci

ActiveCell.Formula = _
        "=IFERROR(VLOOKUP(Range("N2"),SHEETS("IEP S-1").RANGE("M:T")],4,0),""Non"")"

Cdlmnt

Via

via55 · Answer

Le problème c'est que j'avais écrit n'importe quoi je me suis mélangé les pédales entre les instructions Sheets et Range à mettre dans une ligne de macro mais qui n’apparaissent pas dans la formule pour une cellule!!

La bonne écriture est :ActiveCell.Formula = "=IFERROR(VLOOKUP(N2,'IEP S-1'!M:T,4,0),""non"")"

Cdlmnt

Via

La bonne écriture est :ActiveCell.Formula = "=IFERROR(VLOOKUP(N2,'IEP S-1'!M:T,4,0),""non"")"

Cdlmnt

Via

via55 · Answer

Bonsoir Jérôme

1) Écris ton code ainsi :

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "IEP!R1C2:R" & DLIEP  & "C" & CIEP, Version:=6).CreatePivotTable TableDestination:= _
        "TCD!R1C1", TableName:="Tableau croisé dynamique1", DefaultVersion:=6

2) Pour la dernière colonne le code est :

CIEP = Sheets("IEP").Rows(2).Find("*", , , , xlByColumns, xlPrevious).Column

Cdmnt

Via

via55 · Answer

Re,

1) Si tu utilises la même variable DLIEP pour le TCD, comme cette variable contient le n° de la dernière ligne avant suppression des doublons c'est normal que ça prenne toujours l'intégralité des lignes. Après la suppression des doublons et avant le TCD il faut recalculer la dernière ligne non vide avec

DLIEP = Sheets("IEP").Columns(2).Find("*", , , , xlByColumns, xlPrevious).Row

2) R1C2 = ROW1 COLUMN2   soit ligne 1 colonne 2 soit encore B1

3) Pour que ce qui est du placement de tes TCD tes instructions ont à priori l'air correcte, impossible de te dire ce qui ne va pas sans ton fichier complet avec sa macro

Qu'est ce qui ne fonctionne pas,  ? le placement n'est pas bon ? le calcul de la dernière line non vide ne se fait pas ? ..

Quelle valeur est indiquée pour DLTCD3 quand tu passes dessus avec la souris dans le code après arrêt ?

Cdlmnt

Via

via55 · Answer

Comme je te disais je ne peux rien dire de plus sans avoir sous la main ton fichier

Poste ton fichier sur cjoint.com, fais créer un lien que tu copies et reviens coller ici

via55 · Answer

Bonjour Jérôme

HTCD4 = Cells(DLTCD3 + 5, 1).Select  Sélectionne la 1ere colonne donc erreur car coller sur tableau déjà existant

Ce serait plutôt HTCD4 = Cells(DLTCD3 + 5,5).Select

Ensuite les lignes

Selection.End(xlToRight).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E1").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Range("E27").Select

je ne vois pas à quoi elles correspondent et doivent être supprimées je pense

D'autres erreurs surviennent après dans la macro qui je pense provient d'un enregistrement que tu as arrangé

A ta place je supprimerai toute la fin de la macro après ce dernier TCD et après s'être assuré que tout fonctionne jusque là, je reprendrai tranquillement à partir de là (en enregistrant les manips nécessaire s'il y a lieu) et pas à pas pour finaliser une macro qui fonctionnera

Bon courage

Cdlmnt

Via

Jerome · Answer

Bonjour Via,

Trop fort et merci pour ton aide. En faite au départ et via l'enregistreur je créais le TCD1 ensuite je copiais/collais le TCD1 pour faire mon TCD2 et j'ajoutais ce que je voulais. Pour le TCD3, idem je copiais/collais le TCD1 et faisais mon TCD3.

Par contre pour le TCD4, je copiais le TCD3. Sauf que le TCD3 a deux filtres et du coup pour le sélectionner via l'enregistreur j'ai sélectionné la cellule E1 (Nom du filtre1) puis j'ai fait (Shit + ctrl + flèche du bas) une première fois et ça m'a sélectionné le deuxième filtre, j'ai refait (Shit + ctrl + flèche du bas) ce qui m'a emmené en haut de mon TCD3 et je l'ai fait une dernière fois ce qui m'a permis d'arriver en bas. Je l'ai ensuite fait sur la droite (Shit + ctrl + flèche droite) ce qui m'a permis de sélectionner tout le TCD3 et j'ai pu le copier.

Voici le code avec l'enregistreur

Range("E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E1").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Range("E27").Select
    ActiveSheet.Paste

Le problème c'est qu'en VBA, je n'ai pas réussi à sélectionner le TCD3 pour le copier.

J'ai essayé les commandes suivantes, mais ça n'a pas fonctionné. Saurais tu comment je peux copier mon TCD3 qui a deux filtres ?

Range("HTCD3").currentregion.Select ==> qui me sélectionne que les deux filtres

et

HTCD3 = Cells(1, CTCD + 3).End(xlUp).End(xlToRight).Select qui est erroné

Encore merci pour ton aide,

Amicalement

Jérôme

via55 · Answer

Là je ne sais !!

Mais quand on peut pas prendre le droit on prend le gauche

A ta place je continuerais dans  ma macro à copier le TCD1 et je le collerais en bonne place avec l 'instruction maintenant rectifiée

Ensuite en revenant sur le tableau et en enregistrant je ferais toutes les manips nécessaires pour l'adapter selon ce que je veux comme filtre et autre et j'intégrerai ensuite l'enregistrement dans ma macro

Jerome · Answer

Aucun souci et franchement un grand merci pour toute ton aide et la rapidité de tes réponses.

Je vais effectivement toujours repartir de mon TCD1 :-)

Encore merci

Amicalement

Jérôme

Jerome · Answer

Bonjour Via,

Désolé de te solliciter une nouvelle fois, mais j'ai de nouveau besoin de ton aide. Dans mon onglet suivi global, je dois tirer une formule (colonne E) de la dernière cellule remplie à celle d'en dessous. Je voulais trouver la première ligne vide (ou devra être ma "nouvelle" formule)

'''Variable pour sélectionner la première ligne vide (onglet Suivi global) colonne E

ESG = Range("E3").End(xlDown).Row +1

Puis je voulais l'envoyer jusqu'à la dernière cellule remplie de la colonne D (qui en a justement qu'une de plus remplie)

'''Variable derligneSuiviG = Envoie la formule jusqu'à la dernière ligne remplie de la colonne D

derligneSuiviG = Range("D" & Rows.Count).End(xlUp).Row

J'ai ensuite sélectionné la cellule souhaité

Cells(ESG, 5).Select

Et j'ai essayé d'envoyer ma cellule par les propositions ci dessous mais ça n'a pas fonctionné.

Selection.AutoFill Destination:=Range(Cells(ESG, 5), Cells(derligneSuiviG, ESG))

Selection.AutoFill Destination:=Range(Cells(derligneSuiviG, 5))

Selection.AutoFill Destination:=Range(Cells(derligneSuiviG, ESG))

J'ai également essayé ceci

ESG = Range("E3").End(xlDown).Row
    Cells(ESG + 1, 5).Select
    Selection.AutoFill Destination:=Range(Cells(ESG, derligneSuiviG))

Et pareil ça n'a pas fonctionné. Saurais tu ce que j'ai mal exécuté ?

Merci pour ton aide,

Amicalement

Jérôme

via55 · Answer

Re

C'est bien la forme de la 1ere formule qui est la bonne mais c’est n'importe quoi car la variable ESG sert tantôt pour la ligne , tantôt pour la colonne

Selection.AutoFill Destination:=Range(Cells(ESG, 5), Cells(derligneSuiviG, ESG))

Rectifie ta formul ainsi

Selection.AutoFill Destination:=Range(Cells(ESG, 5), Cells(derligneSuiviG, 5))

Cdlmnt

Via

Jerome · Answer

Bonjour Via,

Merci beaucoup, j'ai cherché, cherché, pour ne pas te déranger, mais je n'ai finalement pas eu le choix. Ca marche parfaitement. J'ai un peu compris le principe, mais j'ai encore du mal et je finis toujours par bloquer sur un élément.

Je profite pour te poser une autre question si tu le veux bien ? J'ai une commande qui va chercher les résultats des TCD1/TCD3/TCD5 et les reporte dans mon tableau onglet suivi global.

L'enregistreur m'a écrit cette commande : ActiveCell.FormulaR1C1 = "=GETPIVOTDATA(""IEP - Venue/Passage"",TCD!R29C9)"

Ca fonctionne très bien! Ma seule crainte, c'est que les TCD sont mouvants selon les résultats et peuvent être +/- long et s'il devait être plus long que ce qui est calculé et devait commencer qu'à la ligne par exemple 35 il me renverrait une erreur. Penses tu qu'il soit possible de remplacer le TDC!R29C9 par mon HTCD5 qui correspond à la première cellule du TCD5 ?

Merci pour ton aide,

Amicalement

Jérôme

via55 · Answer

Bonjour

Apparemment R29C9 renvoie à la ligne 29 de la colonne 9, je ne sais pas à quoi cela correspond sur ton fichier mais tu peux modifier les références avec des variables à la place de la ligne et de la colonne

par ex avec  LTCD pour la 1ere ligne de ton TCD et CTCD pour sa colonne   :

ActiveCell.FormulaR1C1 = "=GETPIVOTDATA(""IEP - Venue/Passage""," & "TCD!R" & LTCD & "C" & CTCD & " )"

Cdlmnt

Via

Jerome · Answer

Re,

Oui c'est bien ça, ligne 29 de la colonne 9 qui correspond à mon TCD5.

Du coup, la seule variable que j'ai du TCD5 est celle ci

'Variable Haut TCD5 - Selectionner la deuxième cellule vide à droite du TCD 4
    HTCD5 = Cells(DLTCD3 + 9, 9).Select

J'ai essayé de mettre cette variable pour les colonnes (Exactement celle du TCD1), mais je ne comprends pas quel élément je dois modifier pour l'adapter au TCD5

'''variable dernière colonne remplie de l'onglet TCD du TCD5
    CTCD5 = Sheets("TCD").Rows(1).Find("*", , , , xlByColumns, xlPrevious).Column

et terminer avec cette commande qui n'a pas fonctionné :

ActiveCell.FormulaR1C1 = "=GETPIVOTDATA(""IEP - Venue/Passage""," & "TCD!R" & HTCD5 & "C" & CTCD5 & " )"

Et une deuxième questions, j'ai besoin d'inscrire la date du jour + du texte à coté.

J'ai fait la formule

ActiveCell.FormulaR1C1 = "=TODAY()& "" test"""

Pour copier/coller en valeur sauf que la date se met en code 44759 même quand je met en format date. Saurais tu comment faire ?

Merci pour ton aide,

Amicalement

Jérôme

via55 · Answer

Comme je comprends la cellule en ligne 29 et colonne 9 doit être la 1ere en haut à gauche du TCD

HTCD5 n'est pas chez toi un numéro de ligne, c’est la sélection d'une cellule

par contre CTCD5 renvoie bien un n° de colonne mais ce n'est pas la 1ere du TCD5

IL faut que par formules tu détermines la 1ere ligne et la 1ere colonne de ton TCD5

HTCD5 = ..... et CTCD5=.....

Ensuite pour vérifier que les valeurs retournées sont les bonnes, pour test (tu enlèveras cette ligne après) tu mets MsgBox HTCD5 & " " & CTCD5

Quand tu auras déterminé les bonnes valeurs la formule GETPIVOTDATA... devrait fonctionner

Cdmnt

Via

Jerome · Answer

Re,

Oui c'est bien ça.

La cellule en ligne 29 et colonne 9 est la 1ere en haut à gauche du TCD5. Par contre les TCD4 et TCD5 dépendent de la fin du 3 (selon sa longueur) donc c’est des inconnus au départ.

'''Variable Haut du TCD3 = troisième cellule vide vers la droite après le TCD1

HTCD3 = Cells(1, CTCD + 3).Select

'''Variable dernière ligne remplie de l'onglet TCD du TCD3 de la colonne E

DLTCD3 = Sheets("TCD").Columns(5).Find("*", , , , xlByColumns, xlPrevious).Row

'''Variable haut du TCD4

HTCD4 = Cells(DLTCD3 + 9, 5).Select

'Variable Haut TCD5 - Selectionner la deuxième cellule vide à droite du TCD 4

HTCD5 = Cells(DLTCD3 + 9, 9).Select

Mais du coup dans cette variable, je ne vois pas quoi changer pour trouver la dernière colonne remplie du TCD5

CTCD5 = Sheets("TCD").Rows(1).Find("*", , , , xlByColumns, xlPrevious).Column

Petite question, pourquoi ne puis-je pas utiliser HTCD5 à la fois en colonne et en ligne ? ActiveCell.FormulaR1C1 = "=GETPIVOTDATA(""IEP - Venue/Passage""," & "TCD!R" & HTCD5 & "C" & HTCD5 & " )"

Merci pour ton aide ?

Amicalement

Jérôme

via55 · Answer

CTCD5 = Sheets("TCD").Rows(1).Find("*", , , , xlByColumns, xlPrevious).Column te donne la dernière colonne, toi il te faut la 1ere du TCD5 mais cette colonne est sans doute toujours la même donc tu peux sans doute inscrire le nombre en dur dans la macro

par ex ActiveCell.FormulaR1C1 = "=GETPIVOTDATA(""IEP - Venue/Passage""," & "TCD!R" & HTCD5 & "C9" & " )" si c'est la colonne 9

pourquoi ne puis-je pas utiliser HTCD5 à la fois en colonne et en ligne

parce que la colonne n'est pas la même que la ligne !!  la variable HTCD5 doit donner 29 alors que la colonne c'est 9

Jerome · Answer

Re,

Merci beaucoup. Effectivement C9 est en dur. J'ai essayé

ActiveCell.FormulaR1C1 = "=GETPIVOTDATA(""IEP - Venue/Passage""," & "TCD!R" & HTCD5 & "C9" & " )"

et j'ai une erreur #REF! car il va chercher i255 ???

HTCD5 = normalement I20

Voici la ligne dans le tableur.

=LIREDONNEESTABCROISDYNAMIQUE("IEP - Venue/Passage";TCD!$I$255 )

J’aurais une autre question, j'ai besoin d'inscrire la date du jour + du texte à la suite.

J'ai fait la formule

ActiveCell.FormulaR1C1 = "=TODAY()& "" test"""

Pour copier/coller en valeur sauf que la date se met en code 44759 même quand je met en format date. Saurais tu comment faire ?

Merci pour ton aide,

Amicalement

Jérôme

via55 · Answer

La valeur de HTCD ne peut pas être I20 !!!  je le répète ce doit un nombre donnant le n° de ligne où débute le TCD en l’occurrence 29 d'après ton message précédent

Quand à la date il faut indiquer le format d'écriture du texte :

exemple  ActiveCell.FormulaR1C1 = "=TEXT(TODAY(),""jj/mm/aa"")  & "" ggg"""

mais tu  pourrais mettre "jjjj jj mmmm aaaa" si tu veux une date longue, ou toute autre forme d'écriture que tu souhaite (avec des -, avec le jour en abrégé ..)

Jerome · Answer

Bonjour Via,

Un très très très grand merci. Oui en effet c'était I20, mais je l'avais modifier pour faire un test et vérifier s'il fallait absolument tomber sur le résultat ou si n'importe quelle cellule du TCD fonctionnait.

Par contre, la formule va chercher la ligne i255

ActiveCell.FormulaR1C1 = "=GETPIVOTDATA(""IEP - Venue/Passage""," & "TCD!R" & HTCD5 & "C9" & " )"

=LIREDONNEESTABCROISDYNAMIQUE("IEP - Venue/Passage";TCD!$I$255 )

Super un très grand merci pour la date ça fonctionne parfaitement

Amicalement

Jérôme

via55 · Answer

Bonjour Jérôme

Quelle valeur a la variable HTCD5 ?

Mets comme je te l'avais suggéré une ligne MsgBox juste avant ta formule ActiveCell.... pour voir quelle valeur justement, apparemment c'est 255 mais ça ne correspond pas à la réalité

Quelle est la formule qui te permet de trouver HTCD5 ?

Jerome · Answer

Bonjour Via,

Désolé de ne pas être clair. J'ai créé fichier pour que ce soit peut être plus simple. C'est en fin de la Macro liste TE, travaille sur la ''Feuille Suivi global''

https://www.cjoint.com/c/LGsnmeCGf3D

Sinon entendu, je vais mettre un MSGbox devant mes variables et ça va me renvoyer la cellule c'est bien ça ?

TCD1 = Cells(1, 1).Select

TCD2 = HTCD2 = Cells(DLTCD + 9, 1).Select

TCD3 = HTCD3 = Cells(1, CTCD + 3).Select

DLTCD3 = Sheets("TCD").Columns(5).Find("*", , , , xlByColumns, xlPrevious).Row

HTCD4 = Cells(DLTCD3 + 9, 5).Select

HTCD5 = Cells(DLTCD3 + 9, 9).Select

Encore merci

Amicalement

Jérôme

via55 · Answer

Re,

Comme je te l'ai déjà dit HTCD5 = Cells(DLTCD3 + 9, 9).Select fait une sélection mais ne recherche pas la ligne du TCD5 !!!  Quand on met un Msgbox  HTCD5 (c'est la 1ere chose à faire si on a un doute) cela renvoie bien 255 (pourquoi je ne sais pas !)

En fait ce que tu veux c'est trouver la ligne de l'en-tête du TCD  placé en colonne I, c'est à dire la ligne avec le titre Étiquettes de données, c'est celle-ci dont on a besoin dans la formule

donc avant la ligne

ActiveCell.FormulaR1C1 = "=GETPIVOTDATA(""IEP - Venue/Passage""," & "TCD!R" & HTCD5 & "C9" & " )"

mets cette instruction :

HTCD5== WorksheetFunction.Match("Étiquettes de lignes", Sheets("TCD").Range("I:I"), 0) qui va trouver la bonne ligne

Jerome · Answer

Re,

Merci beaucoup et pour les explications qui sont également très clair. En effet, mes HTCD1/2/3/4/5 sont en effet des sélections et non une recherche... Il faut que je revois toutes mes variables.

Merci pour l'explication des MsgBox, je le ferai dorénavant.

Quant à la dernière exécution, c'est également parfait et je t'en remercie très sincèrement

A bientôt

Amicalement

Jérôme

via55 · Answer

Bien ☺

Bonne suite et à une prochaine fois

Cdlmnt

Via

VBA Excel sélectionner la première colonne vide

29 réponses

Discussions similaires

Newsletters