Sélection de (presque) toute une colonne et autofill (VBA)

Bonjour à tous,

j'avais l'habitude de faire des mises en forme un peu fastidieuse et des calculs à la mano dans excel sur des fichiers ponctuels mais je cherche à automatiser un certain nombre de choses car finalement ce genre de traitement va être récurrent dans l'année ; puis je cherche à en faire une (ou des) macros pour qu'une autre personne qui ne connaît pas excel/VBA (encore moins que moi, je veux dire) ait juste à faire un raccouci clavier pour lancer une macro. Bref.

J'ai beau m'appuyer sur des bouquins de chez Eyrolles (j'ai quand même appris plein de trucs !) ou consulter ce forum (merci et bravo à tous !), il y a toujours des choses qui m'échappent.

Par exemple... J'ai plusieurs classeurs. Je cherche à créer une macro que j'appliquerai à tous les classeurs. Tous ces classeurs ont le même nombre de feuilles, mais sur la même feuille (disons la 1) de chaque classeur il y a un nombre différent de lignes.

Mettons une feuille avec du contenu (allant de 0 à 6 par exemple) dans les 800 premières lignes de la première colonne A et dans un autre classeur idem mais dans les 1300 premières lignes. A la mano dans excel, pour des raisons annexes, je mettais en B2 une formule NB.SI(A2;">0") : donc en gros, si dans la colonne A j'avais une valeur sup à 0, j'avais 1 dans la colonne B, et si la valeur était égale à 0 en A, j'avais 0 en B. Jusque-là tout va bien. Puis je double-cliquais dans le coin inférieur droit de B2 pour avoir un autofill appliquant ma formule NB.SI à toutes les lignes de la colonne B.

Dans le premier classeur, ca allait donc jusqu'à la cellule B800 et dans le deuxième classeur jusqu'à la cellule B1300.

En bidouillant un peu avec VBA je suis d'abord parti là-dessus :

Range("B2").Select

ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-1],"">0"")"

Range("B2").Select

Selection.AutoFill Destination:=Range("B2:B800")

mais ça ne marche pas pour tous les classeurs, puisque le nb de lignes est différent pour chaque classeur ! Si j'applique ça à mon classeur de 1300 lignes, ça ne va pas jusqu'au bout des 1300 lignes puisque ça s'arrête à la ligne 800, comme je lui demande bêtement.

Du coup je cherche comment écrire que je veux que la formule de la cellule sélectionnée s'applique à toutes les cellules dans la même colonne, jusqu'à ce que la cellule correspondante dans la colonne A soit vide... comme quand je faisais mon autofill en double-cliquant en bas à droite de la cellule.

Je ne peux pas juste appliquer la formule sur toute la colonne, avec un genre de B:B, car je ne veux pas que la formule soit appliquée à B1 ou jusqu'à B1048576, mais de B2 à une variable que je ne sais pas exprimer.

j'ai regardé les topics suivants qui ne sont pas loin, mais qui ne sont pas ça.

https://forum.excel-pratique.com/excel/macro-etirer-cellule-jusqu-a-t98174.html

https://forum.excel-pratique.com/excel/copier-une-formule-a-partir-de-la-derniere-cellule-non-vide-t97622.html

Je mets un fichier joint avec deux pages différente d'un même classeur au lieu de deux mêmes pages de deux classeurs différentes, au cas où ça rende les choses plus claires...

Si vous avez des suggestions ou explications, je vous remercie !

EDIT : en fait je crois que c'est un peu le même problème que ça, mais mis à jour avec 2013 : https://forum.excel-pratique.com/excel/selection-autofill-en-ref-relative-t18641.html je vais creuser de mon côté, mais que ça ne vous empêche pas de répondre

Bonjour,

Il me semble que la variable que tu recherches se trouve dans la Colonne A ...

Rien ne t'empêche ... dans ta macro ... de donner à une variable cette valeur ... que tu reprendras dans ta copie ...

Bonjour,

Un exemple de ce que j'ai compris.

ALT F8 puis exécuter la procédure.

Cdlt.

Public Sub DEMO()
Dim ws As Worksheet, n As Long, rng As Range
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            n = .Cells(.Rows.Count, 1).End(xlUp).Row
            Set rng = .Cells(2, "B").Resize(n - 1)
        End With
        With rng
            .FormulaR1C1 = "=N(RC[-1]>0)"
            .Value = rng.Value
        End With
    Next
End Sub

Salut Jean-Eric

Je jalouse au plus haut point ta boule de cristal ...!!!

Merci pour ces réponses !

Je n'ai compris aucun des deux posts de James007

mais je vais tester la formule de Jean-Eric (je vais aussi essayer de la comprendre en la décomposant... pas gagné)

si ça fonctionne je reviendrai cocher le thread.

Re,

Prend le temps qu'il te faudra et reviens vers nous.

Cdlt.

Bonjour,

Je n'ai compris aucun des deux posts de James007

Ce n'est pas grave ...

L'essentiel est que le code de Jean-Eric fonctionne ...

Bon, d'abord, ça marche, merci !

Mais je me suis sans doute mal exprimé, cette formule est presque trop bien pour moi : en effet elle s'applique à toutes les sheets du classeur, alors que je ne veux l'appliquer qu'à l'active worksheet (ou au pire à une sheet que je nomme). Je suppose que puisque je veux que ça ne s'applique qu'à l'Active Worksheet, la désignation est facultative.

Je pense que je n'ai pas correctement compris la moitié de ce que tu as fait, mais si j'essaie de lire ton code :

*avec Dim, tu déclares des variables : ws comme Worksheet, n de type Long, et rng comme Range.

->Mais moi je ne devrais pas avoir besoin de définir la variable Worksheet, c'est ça ?

*For Each : là tu dis en gros "pour toutes les feuilles du classeur actif"

• with ws
• -tu désignes que n est égal à un nombre de cellules (.Cells) défini comme le nombre de lignes (.Rows.Count) dans la 1ère colonne (donc la A). En revanche la fin reste floue : (.End(xlUp)) je vois vaguement que ça sert à définir la dernière cellule occupée mais pourquoi Up plutôt que Down, ça m'échappe. Et même très flou pour le dernier .Row or je soupçonne que c'est précisément pour ça que je ne comprends pas tout.
• -tu affectes à la Range rng une zone multicellule qui part de la ligne 2, colonne B (donc cellule B2) et dont la taille est égale à n-1

• with rng
• -tu mets la formule mais je ne comprends pas le .Value = rng.Value

*Next : je comprends qu'avec le For Each au début et le Next à la fin, c'est supposé être une instruction itérative. Mais je ne comprends pas en quoi le bazar est une itération sauf à dire que la boucle vérifie à chaque ligne si dans la colonne A il y a le truc pour la formule dans la colonne B. Là je me dis que c'est lié au .Row et au Up que je n'ai pas compris plus haut. Ou alors c'est pour passer en revue les worksheets du classeur les unes après les autres ?

Et dire que tout ça ne concerne que ma première question... parce que je risque d'avoir d'autres formules à éclaircir dans le futur ! J'essaie de comprendre et pas seulement de copier/coller...

Merci encore !

Re,

Un nouvel exemple commenté.

A te relire.

Cdlt.

Public Sub DEMO2()
Dim ws As Worksheet, n As Long, rng As Range
    'Nom feuille à définir
    Set ws = ActiveWorkbook.Worksheets("A définir")
    'ou
    'Set ws = ActiveSheet
        With ws
            'Dernière ligne vide en partant du bas
            'Avec xlDown, on a le souci des cellules vides !
            n = .Cells(.Rows.Count, 1).End(xlUp).Row
            'Plage de cellule B2:B(n-1)
            Set rng = .Cells(2, "B").Resize(n - 1)
        End With
        With rng
            'On applique la formule à la plage entière
            .FormulaR1C1 = "=N(RC[-1]>0)"
            'On remplace les formules par leurs résultats
            .Value = rng.Value
        End With
End Sub

Yeah, merci pour cette réponse et pour les commentaires !

Je comprends mieux, notamment le Value = rng.Value, pourtant évident !

Bonjour,

A bientôt pour de nouvelles questions.