Sub appliquer_formule()
Dim DL As Integer
DL = Cells(Application.Rows.Count, "A").End(xlUp).Row
Range("J2").FormulaR1C1 = "=IF(LEFT(A2;4)=""Bat "";""Bâtiment ""&RIGHT(A2;LENB(A2)-4))"
Range("J2").AutoFill Destination:=Range("J2:J" & DL), Type:=xlFillDefault
End Sub