Plages variables et multiples regressions

Le 13/07/2018 à 12:08

Bonjour à tous,

J'ai un code qui effectue plusieurs régressions linéaires avec des séries dont la longueur est non fixe.

J'ai 18 variables à expliquer (colonne C à U) que je régresse avec 1 variable explicative (colonne B)

Option Explicit
Sub Calcul_régression()
'
' Calcul_régression Macro
'Suprime les données et les bordures des précédentes regressions
'Sélectionne l'ensemble des données non vides d'une colonne
'Calcule l'ensemble des régressions des différents secteurs et enregistre les données dans la feuille "regression taux longs"
'Copie le nom du secteur correspondant à la regession dans la feuille "données" et le colle en face de la cellule "Rapport détaillé" de la feuille Reg_taux_longs

Dim dest As Worksheet
Dim source As Worksheet
Dim dernlig As Long

Set dest = ThisWorkbook.Sheets("Reg_taux_longs")
Set source = ThisWorkbook.Sheets("Données")
dernlig = source.Range("C" & Rows.Count).End(xlUp).Row
dest.Cells.ClearContents
dest.Cells.Borders(xlDiagonalDown).LineStyle = xlNone
dest.Cells.Borders(xlDiagonalUp).LineStyle = xlNone
dest.Cells.Borders(xlEdgeLeft).LineStyle = xlNone
dest.Cells.Borders(xlEdgeTop).LineStyle = xlNone
dest.Cells.Borders(xlEdgeBottom).LineStyle = xlNone
dest.Cells.Borders(xlEdgeRight).LineStyle = xlNone
dest.Cells.Borders(xlInsideVertical).LineStyle = xlNone
dest.Cells.Borders(xlInsideHorizontal).LineStyle = xlNone

     Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$C$5:$C$" & dernlig), _
        source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$1:$I$18"), False, False, False, False, , False
        dest.Range("B1").FormulaR1C1 = "=Données!R[3]C[1]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$D$5:$D$" & dernlig), _
        source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$20:$I$38"), False, False, False, False, , False
        dest.Range("B20").FormulaR1C1 = "=Données!R[-16]C[2]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$E$5:$E$" & dernlig), _
        source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$40:$I$58"), False, False, False, False, , False
        dest.Range("B40").FormulaR1C1 = "=Données!R[-36]C[3]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$F$5:$F$" & dernlig), _
        source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$60:$I$78"), False, False, False, False, , False
        dest.Range("B60").FormulaR1C1 = "=Données!R[-56]C[4]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$G$5:$G$" & dernlig), _
        source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$80:$I$98"), False, False, False, False, , False
        dest.Range("B80").FormulaR1C1 = "=Données!R[-76]C[5]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$H$5:$H$" & dernlig), _
        source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$100:$I$118"), False, False, False, False, , False
        dest.Range("B100").FormulaR1C1 = "=Données!R[-96]C[6]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$I$5:$I$" & dernlig), _
        source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$120:$I$138"), False, False, False, False, , False
        dest.Range("B120").FormulaR1C1 = "=Données!R[-116]C[7]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$J$5:$J$" & dernlig), _
        source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$140:$I$158"), False, False, False, False, , False
        dest.Range("B140").Select
    ActiveCell.FormulaR1C1 = "=Données!R[-136]C[8]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$K$5:$K$" & dernlig), _
        source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$160:$I$178"), False, False, False, False, , False
        dest.Range("B160").FormulaR1C1 = "=Données!R[-156]C[9]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$L$5:$L$" & dernlig), _
        source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$180:$I$198"), False, False, False, False, , False
        dest.Range("B180").FormulaR1C1 = "=Données!R[-176]C[10]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$M$5:$M$" & dernlig), _
        source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$200:$I$218"), False, False, False, False, , False
        dest.Range("B200").FormulaR1C1 = "=Données!R[-196]C[11]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$N$65:$N$" & dernlig), _
        source.Range("$B$65:$B$" & dernlig), False, False, , dest.Range("$A$220:$I$238"), False, False, False, False, , False
        dest.Range("B220").FormulaR1C1 = "=Données!R[-216]C[12]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$O$65:$O$" & dernlig), _
        source.Range("$B$65:$B$" & dernlig), False, False, , dest.Range("$A$240:$I$258"), False, False, False, False, , False
        dest.Range("B240").FormulaR1C1 = "=Données!R[-236]C[13]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$P$173:$P$" & dernlig), _
        source.Range("$B$173:$B$" & dernlig), False, False, , dest.Range("$A$260:$I$278"), False, False, False, False, , False
        dest.Range("B260").FormulaR1C1 = "=Données!R[-256]C[14]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$Q$65:$Q$" & dernlig), _
        source.Range("$B$65:$B$" & dernlig), False, False, , dest.Range("$A$280:$I$298"), False, False, False, False, , False
        dest.Range("B280").FormulaR1C1 = "=Données!R[-276]C[15]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$R$5:$R$" & dernlig), _
        source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$300:$I$318"), False, False, False, False, , False
        dest.Range("B300").FormulaR1C1 = "=Données!R[-296]C[16]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$S$5:$S$" & dernlig), _
        source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$320:$I$338"), False, False, False, False, , False
        dest.Range("B320").FormulaR1C1 = "=Données!R[-316]C[17]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$T$65:$T$" & dernlig), _
        source.Range("$B$65:$B$" & dernlig), False, False, , dest.Range("$A$340:$I$358"), False, False, False, False, , False
        dest.Range("B340").FormulaR1C1 = "=Données!R[-336]C[18]"

    Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$U$5:$U$" & dernlig), _
        source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$360:$I$378"), False, False, False, False, , False
        dest.Range("B360").FormulaR1C1 = "=Données!R[-356]C[19]"
End Sub

Ce code marche, mais je souhaiterais l'optimiser car je répète 18 fois la même actions ...

Je pense avoir trouvé comment passer d'une colonne à l'autre automatiquement.

Option Explicit
Sub Calcul_régression()
'
' Calcul_régression Macro
'Suprime les données et les bordures des précédentes regressions
'Sélectionne l'ensemble des données non vides d'une colonne
'Calcule l'ensemble des régressions des différents secteurs et enregistre les données dans la feuille "regression taux longs"
'Copie le nom du secteur correspondant à la regession dans la feuille "données" et le colle en face de la cellule "Rapport détaillé" de la feuille Reg_taux_longs

Dim dest As Worksheet
Dim source As Worksheet
Dim dernlig As Long
Dim derncol As String
Dim T
Dim a$
a$ = Range("C1").SpecialCells(xlCellTypeLastCell).Address
T = Split(a$, "$", -1)
derncol = T(1)

Set dest = ThisWorkbook.Sheets("Reg_taux_longs")
Set source = ThisWorkbook.Sheets("Données")
dernlig = source.Range("C" & Rows.Count).End(xlUp).Row
derncol = Cells(1, Cells.Columns.Count).End(xlToLeft).Column
dest.Cells.ClearContents
dest.Cells.Borders(xlDiagonalDown).LineStyle = xlNone
dest.Cells.Borders(xlDiagonalUp).LineStyle = xlNone
dest.Cells.Borders(xlEdgeLeft).LineStyle = xlNone
dest.Cells.Borders(xlEdgeTop).LineStyle = xlNone
dest.Cells.Borders(xlEdgeBottom).LineStyle = xlNone
dest.Cells.Borders(xlEdgeRight).LineStyle = xlNone
dest.Cells.Borders(xlInsideVertical).LineStyle = xlNone
dest.Cells.Borders(xlInsideHorizontal).LineStyle = xlNone

For i = C To derncol
     Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$i$5:$i$" & dernlig), _
        source.Range("$B$5:$B$" & dernlig), False, False, , dest.Range("$A$1:$G$18"), False, False, False, False, , False
        dest.Range("B1").FormulaR1C1 = "=Données!R[3]C[1]"

Next
        End Sub

Seulement, j'ai trois problématiques:

- La variable à expliquer i (pour rappel il y en a 18) commence soit à la ligne 5 et soit à la ligne 65 ou 173, comment faire commencer la régression à partir de la première cellule non vide de i. Par exemple, lorsqu'une variable à expliquer i commence à la ligne 65, la variable à expliquer sera source.Range("$B$65:$B$" & dernlig)

- Si le résultat de la première régression doit bien être stocké dans ("$A$1:$G$18"), je souhaiterais stocker les autres résultats dans des plages de 18 lignes et 7 colonnes automatiquement et espacer ces plages avec 1 ligne vide

- Ensuite, je copie colle le nom de la régression (qui est l'entête de la colonne i) à la 1ere ligne et deuxième colonne de la dite plage

Quelqu'un saurait-il comment faire une ou plusieurs de ces opérations?

Merci d'avance!

5perf-relative-secteurs-v3.xlsm (484.39 Ko)
Le 13/07/2018 à 12:18

Bonjour,

Attention à la manière d'écrire. Tu créer une variable i, mais dans le code, quand tu écris "i", elle est lue comme étant le caractère i.

En résumé :

"$i$5:$i$" & dernlig

Est différent de ;

"$" & i & "$5:$" & i & "$" & dernlig

Aussi, ta boucle est mal écrite :

For i = C To derncol

La valeur i est un caractère, et tu vas générer une erreur car VBA ne sait pas incrémenter un format string dans une boucle. C'est comme si tu demandais d'aller de "Fromage" à "Avion de chasse", quelle est la succession logique entre ces 2 valeurs ? Pour faire ce que tu souhaites, je ton conseille d'utiliser la fonction chr(i), qui renvoie le caractère correspondant à un chiffre. Comme ça dans ta boucle i, i est numérique mais converti en caractère par la suite.

Le 13/07/2018 à 12:40

J'ai modifié ton code mais sans le tester. Je n'ai pas touché à certaines parties, n'ayant pas cherché à comprendre l'utilité de toutes les instructions.

Sub Calcul_régression2()

    'Calcul_régression Macro
    'Suprime les données et les bordures des précédentes regressions
    'Sélectionne l'ensemble des données non vides d'une colonne
    'Calcule l'ensemble des régressions des différents secteurs et enregistre les données dans la feuille "regression taux longs"
    'Copie le nom du secteur correspondant à la regession dans la feuille "données" et le colle en face de la cellule "Rapport détaillé" de la feuille Reg_taux_longs

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim dest As Worksheet, source As Worksheet
    Dim premlig As Integer, dernlig As Integer

    Set dest = ThisWorkbook.Sheets("Reg_taux_longs")
    Set source = ThisWorkbook.Sheets("Données")
    dernlig = source.Range("C" & Rows.Count).End(xlUp).Row

    With dest.Cells
        .ClearContents
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlEdgeLeft).LineStyle = xlNone
        .Borders(xlEdgeTop).LineStyle = xlNone
        .Borders(xlEdgeBottom).LineStyle = xlNone
        .Borders(xlEdgeRight).LineStyle = xlNone
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With

    For i = 3 To 21
        premlig = source.Cells(5, i).End(xlUp).Row
        If premlig > 180 Then premlig = 5
        Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$" & Chr(64 + i) & "$" & premlig & ":$" & Chr(64 + i) & "$" & dernlig), _
            source.Range("$B$" & premlig & ":$B$" & dernlig), False, False, , dest.Range("$A$1:$G$18"), False, False, False, False, , False
            dest.Range("B1").FormulaR1C1 = "=Données!R[3]C[1]"
    Next i

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub
Le 13/07/2018 à 16:17

Merci beaucoup pour tes réponses!

J'ai modifié le code de la façons suivantes et j'ai mieux expliquer les différents blocs de code

Option Explicit
Sub Calcul_régression()
'
' Calcul_régression Macro
'Suprime les données et les bordures des précédentes regressions
'Sélectionne l'ensemble des données non vides d'une colonne
'Calcule l'ensemble des régressions des différents secteurs et enregistre les données dans la feuille "regression taux longs"
'Copie le nom du secteur correspondant à la regession dans la feuille "données" et le colle en face de la cellule "Rapport détaillé" de la feuille Reg_taux_longs

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim dest As Worksheet
Dim source As Worksheet
Dim premlig As Long, dernlig As Long, derncol As Long
Dim ad As String
Dim T
Dim a$

'bloc qui efface les précédents résultats
Set dest = ThisWorkbook.Sheets("Reg_taux_longs")
Set source = ThisWorkbook.Sheets("Données")
dernlig = source.Range("C" & Rows.Count).End(xlUp).Row 'la longueur des séries est variables
derncol = source.Cells(1, Cells.Columns.Count).End(xlToLeft).Column 'le nombre de variables explicatives et à expliquer peut varier
dest.Cells.ClearContents
dest.Cells.Borders(xlDiagonalDown).LineStyle = xlNone
dest.Cells.Borders(xlDiagonalUp).LineStyle = xlNone
dest.Cells.Borders(xlEdgeLeft).LineStyle = xlNone
dest.Cells.Borders(xlEdgeTop).LineStyle = xlNone
dest.Cells.Borders(xlEdgeBottom).LineStyle = xlNone
dest.Cells.Borders(xlEdgeRight).LineStyle = xlNone
dest.Cells.Borders(xlInsideVertical).LineStyle = xlNone
dest.Cells.Borders(xlInsideHorizontal).LineStyle = xlNone

' boucles qui effectue autant de régressions qu'il y a de variables à expliquer
For i = 3 To derncol

    premlig = source.Cells(5, i).End(xlUp).Row 'Donne le numéro de la première ligne non vide de la colonne i

    a$ = premlig.SpecialCells(xlCellTypeLastCell).Address 'adresse de la première ligne
    T = Split(a$, "$", -1)
    ad = T(1) 'lettre de colonne de la première ligne

     Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$" & ad & "$premlig:$" & ad & "$" & dernlig), _
        source.Range("$B$" & premlig & ":$B$" & dernlig), False, False, , dest.Range("$A$1:$I$18"), False, False, False, False, , False
        dest.Range("B1").FormulaR1C1 = "=Données!R[3]C[1]"
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Je ne suis pas sûre de ma ligne de code qui renvoie le numéro de la première ligne non vide d'une colonne i...

Pedro22 a écrit :

J'ai modifié ton code mais sans le tester. Je n'ai pas touché à certaines parties, n'ayant pas cherché à comprendre l'utilité de toutes les instructions.

Sub Calcul_régression2()

    'Calcul_régression Macro
    'Suprime les données et les bordures des précédentes regressions
    'Sélectionne l'ensemble des données non vides d'une colonne
    'Calcule l'ensemble des régressions des différents secteurs et enregistre les données dans la feuille "regression taux longs"
    'Copie le nom du secteur correspondant à la regession dans la feuille "données" et le colle en face de la cellule "Rapport détaillé" de la feuille Reg_taux_longs

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim dest As Worksheet, source As Worksheet
    Dim premlig As Integer, dernlig As Integer

    Set dest = ThisWorkbook.Sheets("Reg_taux_longs")
    Set source = ThisWorkbook.Sheets("Données")
    dernlig = source.Range("C" & Rows.Count).End(xlUp).Row

    With dest.Cells
        .ClearContents
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlEdgeLeft).LineStyle = xlNone
        .Borders(xlEdgeTop).LineStyle = xlNone
        .Borders(xlEdgeBottom).LineStyle = xlNone
        .Borders(xlEdgeRight).LineStyle = xlNone
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With

    For i = 3 To 21
        premlig = source.Cells(5, i).End(xlUp).Row
        If premlig > 180 Then premlig = 5
        Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$" & Chr(64 + i) & "$" & premlig & ":$" & Chr(64 + i) & "$" & dernlig), _
            source.Range("$B$" & premlig & ":$B$" & dernlig), False, False, , dest.Range("$A$1:$G$18"), False, False, False, False, , False
            dest.Range("B1").FormulaR1C1 = "=Données!R[3]C[1]"
    Next i

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub
Le 13/07/2018 à 16:23

As-tu testé mon code tel que je l'ai envoyé ? Je regarde à ça tout à l'heure, mais tu as rajouté des instructions incorrectes, notamment en lien avec mon premier commentaire sur l'utilisation des chaines de caractères et des variables combinées.

Le 13/07/2018 à 17:00
Sub Calcul_régression2()

    Application.ScreenUpdating = False 'Désactive l'affichage le temps d'exécuter la macro
    Application.Calculation = xlCalculationManual 'Désactive le recalcul automatique des formules Excel à chaque modification 

    Dim dest As Worksheet, source As Worksheet
    Dim premlig As Integer, dernlig As Integer

    On Error GoTo Fin

    Set dest = ThisWorkbook.Sheets("Reg_taux_longs")
    Set source = ThisWorkbook.Sheets("Données")
    dernlig = source.Range("C5").End(xlDown).Row '*
    '* Il faut choisir entre 2 méthodes :
    '    --> source.Range("C5").End(xlDown).Row , "xlDown" ici car on se place au début du tableau et on descend chercher la dernière ligne
    '    --> source.UsedRange.Rows.Count

    With dest.Cells 'A privilégier lorsque tu appliques une série d'instruction à un même objet
        .ClearContents
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlEdgeLeft).LineStyle = xlNone
        .Borders(xlEdgeTop).LineStyle = xlNone
        .Borders(xlEdgeBottom).LineStyle = xlNone
        .Borders(xlEdgeRight).LineStyle = xlNone
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With

    For i = 3 To 21 'Boucle sur le n° de colonne (attention, la colonne 21, ou "U" n'est pas la dernière, donc pas de "derncol")
        premlig = source.Cells(5, i).End(xlDown).Row
        If premlig > 180 Then premlig = 5 'Cas où pas de cellule vide entre en-tête et 1ère donnée
        Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$" & Chr(64 + i) & "$" & premlig & ":$" & Chr(64 + i) & "$" & dernlig), _
            source.Range("$B$" & premlig & ":$B$" & dernlig), False, False, , dest.Range("$A$1:$G$18"), False, False, False, False, , False
            dest.Range("B1").FormulaR1C1 = "=Données!R[3]C[1]"
    Next i

Fin:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub
Le 16/07/2018 à 10:07

Bonjour Pedro22,

Merci une fois de plus pour ta réponse et pour les annotations sur ton code qui me permettent de mieux le comprendre. Il y a 2 éléments que je ne comprends pas très bien:

- En ce qui concerne le nombre de colonnes, vu qu'il peut varier n'est-il pas préférable de creer une variable derncol et de faire évoluer la boucle faire For entre i=3 et i=derncol?

- Je ne comprends pas d'où viennent le 180 de la commande if et le 64 de la commande Range

Merci encore pour tes éclairages!

Pedro22 a écrit :
Sub Calcul_régression2()

    Application.ScreenUpdating = False 'Désactive l'affichage le temps d'exécuter la macro
    Application.Calculation = xlCalculationManual 'Désactive le recalcul automatique des formules Excel à chaque modification 

    Dim dest As Worksheet, source As Worksheet
    Dim premlig As Integer, dernlig As Integer

    On Error GoTo Fin

    Set dest = ThisWorkbook.Sheets("Reg_taux_longs")
    Set source = ThisWorkbook.Sheets("Données")
    dernlig = source.Range("C5").End(xlDown).Row '*
    '* Il faut choisir entre 2 méthodes :
    '    --> source.Range("C5").End(xlDown).Row , "xlDown" ici car on se place au début du tableau et on descend chercher la dernière ligne
    '    --> source.UsedRange.Rows.Count

    With dest.Cells 'A privilégier lorsque tu appliques une série d'instruction à un même objet
        .ClearContents
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlEdgeLeft).LineStyle = xlNone
        .Borders(xlEdgeTop).LineStyle = xlNone
        .Borders(xlEdgeBottom).LineStyle = xlNone
        .Borders(xlEdgeRight).LineStyle = xlNone
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With

    For i = 3 To 21 'Boucle sur le n° de colonne (attention, la colonne 21, ou "U" n'est pas la dernière, donc pas de "derncol")
        premlig = source.Cells(5, i).End(xlDown).Row
        If premlig > 180 Then premlig = 5 'Cas où pas de cellule vide entre en-tête et 1ère donnée
        Application.Run "ATPVBAEN.XLAM!Regress", source.Range("$" & Chr(64 + i) & "$" & premlig & ":$" & Chr(64 + i) & "$" & dernlig), _
            source.Range("$B$" & premlig & ":$B$" & dernlig), False, False, , dest.Range("$A$1:$G$18"), False, False, False, False, , False
            dest.Range("B1").FormulaR1C1 = "=Données!R[3]C[1]"
    Next i

Fin:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub
Le 16/07/2018 à 10:38

Bonjour,

Je n'avais pas compris que ton nombre de colonnes pouvais varier. Néanmoins, ta dernière variable Y (colonne "U") n'est pas la colonne la plus à droite dans ta feuille, donc difficile d'automatiser sa détection. A moins que cette colonne ait toujours un nom bien défini ou que tu t'arranges pour que ce soit la dernière.

Le cas échéant, une simple commande interrogeant l'utilisateur devrait suffir :

derncol = Application.InputBox("N° de la dernière colonne ?", Type:=1)

Pour les 2 nombres que tu ne comprends pas :

- 180 est définit arbitrairement pour éviter le cas de figure où ta première valeur Y1 est située juste sous l'en-tête de colonne, sans cellule vide. Pour mieux comprendre le soucis, exécute cette petite macro de test.

Sub TestPremlig()
    Dim Source As Worksheet, i As Byte
    Set source = ThisWorkbook.Sheets("Données")
    For i = 3 To 21 
            premlig = source.Cells(5, i).End(xlDown).Row
            MsgBox("La première ligne de la plage de données est " & premlig)
    Next i
End Sub

Tu peux aussi remplacer le "> 180" par "= dernlig" pour éviter ce cas de figure.

- 64 correspond à l'index de la fonction "chr(index)" qui renvoi un caractère.

'quand i = 3
Chr(64 + i) = "C"

PS : attention avec cette formule, dans le cas où tu as plus de 26 colonnes. Il faudra rajouter des lignes de codes pour écrire les colonnes de type "AA", "AB"...

Le 16/07/2018 à 10:57

C'est beaucoup clair! Merci beaucoup...

Une autre question, penses-tu que si j'utilise la commande suivante

premlig = Columns(i).Find("*", , , , xlByRows, xlNext)

.Row j'aurais toujours besoin de la commande if?

Le 16/07/2018 à 10:59

Je ne maîtrise pas cette commande, mais vue comment elle est écrite, elle risque de te donner le n° de ligne de ton en-tête ! La ligne de commande "If" te pose un réel problème ?

Rechercher des sujets similaires à "plages variables multiples regressions"