View original (French)

VBA: Extend formula to the last row with macro

Solved
Emi80110 Posted messages 69 Status Member -  
ccm81 Posted messages 11033 Status Member -

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_ Posted messages 1823 Status Member 511
     

    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
  2. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     

    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
  3. Emi80110 Posted messages 69 Status Member 2
     

    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
  4. Emi80110 Posted messages 69 Status Member 2
     

    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