Première ligne vide, formule Excel intégrée dans le VBA & autres

Bonjour,

Je viens solliciter votre aide concernant quelques difficultés dans le code VBA que j'essaie de faire (je suis clairement une amatrice, il est possible que mon code ne soit ni beau, ni efficace, ni parfait) pour optimiser un processus dans mes tâches dans l'entreprise dans laquelle je suis. J'ai tout modifié et anonymisé pour qu'il n'y ait aucun problème. Le code plus bas fonctionne (visuellement sur la feuille pour une amatrice, peut-être pas d'un point de vu d'expert).

Les problèmes sont les suivants :

1. Première ligne vide

Lorsque le code a terminé toutes ses tâches, la colonne A se retrouve dupliqué en dessous. J'aimerais à ce moment, lui indiquer qu'il faut ajouter une série de chiffres (spécifiques, ici disons 444) à la première ligne vide) dans la colonne E jusqu'à la fin de la copie de la colonne A. Sachant que je ne peux pas lui indiquer une ligne précise, puisqu'en fonction des mois, je n'ai pas le même nombre de lignes. Sur ce petit exemple, j'aimerais qu'il comprenne qu'il doit commencer à écrire dans la première ligne vide (ligne 4) dans la colonne E, et qu'il doit s'arrêter quand il n'y a plus rien d'écrit (ici la dernière ligne serait la ligne 6) :

2. Formule Excel intégrée dans le VBA

Quand le code a terminé ses tâches on peut voir qu'une colonne apparait à la fin "Centre". Un code doit apparaitre en fonction de la première colonne. Par exemple, le code du centre de Paris serait 15, Seoul 21 et Londres 19. En formule Excel je sais parfaitement le faire, cependant lorsque je demande de rentrer la formule dans le VBA, il m'indique une erreur (à cause des "). Je ne sas donc pas comment intégrer automatiquement cette formule ou une formule similaire, afin de mettre directement le code du centre en fonction de la ville. Ma formule Excel est la suivante (elle a été modifiée aussi pour des raisons de sécurité et d'anonymisation) :

=SI.CONDITIONS(ESTNUM(CHERCHE("Paris";A2));15;ESTNUM(CHERCHE("Seoul";A2));21;ESTNUM(CHERCHE("Londres";A2));19)

3. Propreté du code

Comme vous pouvez le voir plus bas, c'est un vrai fouilli. Certaines choses marchent, mais sous certaines conditions. Par exemple la première ligne ne m'arrange pas, puisqu'elle empêche les autres feuilles qui n'auraient pas ce nom de pouvoir utiliser la macro. De plus, comme je suis vraiment une débutante, j'ai copié-collé des bouts de code un peu partout en croisant les doigts pour que ça fonctionne, en utilisant en même temps la fonction "Enregistrer une macro". Enfin voilà... Mon code ne ressemble à rien et est parfois contraignant, je ne sais pas vraiment comment le rendre plus propre et efficace.

ActiveWorkbook.Worksheets("LYRA MODIFIE").Sort.SortFields.Clear

4. Ouvrir l'utilisation aux non-initiés

Je suis clairement celle qui s'y connait le mieux en Excel dans mon service (même si c'est pas fou-fou), j'aimerais donc donner la possibilité aux non-initiés de l'utiliser plus facilement que ce que je fais moi (j'ai enregistré la macro dans un fichier, que je rajoute chaque nouvelle feuille que je crée à chaque export, via le Visual Basic, clic-droit, "Importer un fichier"). Y'aurait-il un moyen de rendre cette manip plus facile, ou de créer une interface plus accessible ? La seule idée que je vois est de créer un classeur avec cette macro, puis de rajouter les exports dans une feuille à l'intérieur. Seuls les exports seraient supprimés, pas la "page d'accueil".

Le code en question :

Sub Lyraco1()
'
' Lyraco1 Macro
'

'
    Cells.Select
    ActiveWorkbook.Worksheets("LYRA MODIFIE").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("LYRA MODIFIE").Sort.SortFields.Add2 Key _
        :=Range("A2:A61"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("LYRA MODIFIE").Sort
        .SetRange Range("A1:W61")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("B:B").Select
    Selection.NumberFormat = "m/d/yyyy"
    Columns("C:C").Select
    Selection.Replace What:="LC ", Replacement:="LC", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:M").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:N").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").EntireColumn.AutoFit
    Columns("F:F").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("A:A").EntireColumn.AutoFit
    Columns("E:E").Select
    Selection.Cut
    Columns("H:H").Select
    ActiveSheet.Paste
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Compte"
Dim ws As Worksheet: Set ws = ActiveWorkbook.Sheets("LYRA MODIFIE")
Dim lRow As Long: lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

Dim rngToFill As Range
Set rngToFill = ws.Range("E2:E" & lRow)

rngToFill.Value = "6275100"

    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Centre"

    Range("A2:C2" & lRow).Select
    Selection.Copy
    Range("A" & lRow + 1).Select
    ActiveSheet.Paste

    Range("D:D,F:F,G:G,H:H").Select
    Range("H1").Activate
    Selection.Style = "Currency"
End Sub

Si jamais il y a besoin de plus d'explications, n'hésitez pas. En tout cas merci beaucoup de m'avoir lu jusqu'ici, et j'espère que quelqu'un saura trouver des réponses à mes problèmes

Bonjour,

Il ne faut pas vous mortifier, tout le monde est passé par là...

Plutôt que de passer par VBA, vu ce que fait votre code, il serait plus efficace de passer par Power Query. Vous n'aurez pas besoin de VBA. L'apprentissage sera un peu dur au départ, mais tellement simple une fois compris. La technique est tout à fait adaptée pour mettre en forme des données et répond à votre point 4, en lui ajoutant quelques lignes pour la rendre paramétrique. Il existe des tutos très bien faits pour expliquer la technique en y allant pas à pas.

Le fichier joint contient une ébauche d'une requête PQ : Tri par ville, changement du format date, suppression des colonnes Truc.

On crée une requête PQ à partir du menu Données d'Excel. On accède à l'éditeur PQ soit Alt-F12 soit par Données/Requêtes et connexions. Il faut que votre tableau source soit un tableau structuré. Le vôtre s'appelle "t_Lycra".

Bonjour Eric,

Merci pour ta réponse ! Je n'avais pas du tout pensé au Power Query, j'en avais juste vaguement entendu parler. Je vais donc aller chercher de ce côté pour optimiser ce genre de tâches, et effectivement ce sera mieux pour notre service que le VBA très certainement.