View original (French)

VBA: Extend formula to the last row with macro

Solved
Emi80110 -  

Hello :-)

I'm back, with a new request.

I noticed that several discussions have been opened on the same topic, but I’m not warning to reuse the codes given in response.

In the attached file, I would simply like the formulas in columns I to M to extend automatically to the last row of data.

I have reduced the file here for the example, but it's an extraction that can range from 500 to 300,000 rows. It's quite random.

Could you please help me?

Example file: https://cjoint.com/c/LHrlQmZ8Mdo

Thank you in advance :-)

Emilie

5 answers

  1.  ozone_

    Hello,

    Another example :

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

    Placed in a module

    Sub autoFill_column() Range("I2:M2").Select Selection.AutoFill Destination:=Range("I2:M" & Range("A1").End(xlDown).Row), Type:=xlFillDefault End Sub
    1
    1. Mike-31

      Hello,

      I have placed the code in a button

      For column I, which is column 9

      Private Sub CommandButton1_Click() Derlig = Range("A" & Rows.Count).End(xlUp).Row For i = 1 To Derlig Cells(i, 9).FormulaLocal = "=VLOOKUP(G2;'Feuil1 (2)'!A:B;2;0)" Next i End Sub

      and the same for columns J, K, L and M

      See you later
      Mike-31

      I am responsible for what I say, not for what you understand...

      0
      1. Emi80110 Author

        Thank you for your response :-)

        I achieved it with the following code:

        Sub ETAPE_1()
        '
        ' ETAPE_1 Macro
        '
        Range("I5").AutoFill Destination:=Range("I5:I" & (Range("A" & Rows.Count).End(xlUp).Row))
        Range("J5").AutoFill Destination:=Range("J5:J" & (Range("A" & Rows.Count).End(xlUp).Row))
        Range("K5").AutoFill Destination:=Range("K5:K" & (Range("A" & Rows.Count).End(xlUp).Row))
        Range("L5").AutoFill Destination:=Range("L5:L" & (Range("A" & Rows.Count).End(xlUp).Row))
        Range("M5").AutoFill Destination:=Range("M5:M" & (Range("A" & Rows.Count).End(xlUp).Row))
        '
        End Sub

        Thank you very much

        0
        1. Emi80110 Author

          Hello

          oh there it is, it was that simple !!

          I was racking my brains rewriting the same code for each column !!

          thank you very much :-)

          problem solved :-)

          0