XL 2010 VBA Modifier formule dans une macro

[jlbcall]

Bonjour à tous,

Je souhaiterais modifier les 2 formules inclus dans la macro ci dessus de telle sorte que la selction ne se fasse pas sur toutes les colonnes mais à partir de la ligne 2 jusqu’à la dernière utilisée.
Pour se faire j'ai donc la variable DernLigne et je voudrais intégrer cette variable dnas mes formule.
Ma Formule est
Pour la ligne 2 =1/NB.SI.ENS(A:A;A2;B:B;B2;G:G;G2)
et je voudrais
=1/NB.SI.ENS(A2:A & DernLigne;A2;B2:B & DernLigne;B2;G2:G& DernLigne ;G2)
Fichier ci joint

Merci de votre aide et bon week
Sub NbrS()

Dim DernLigne As Integer

With Sheets("Data")
'DernLigne = .Range("A" & .Rows.Count).End(xlUp).Row
Range("Al1").FormulaR1C1 = "SA"
Range("Am1").FormulaR1C1 = "VE"
Range("Al2").Select
DernLigne = ActiveSheet.UsedRange.Rows.Count

ActiveCell.FormulaR1C1 = "=1/COUNTIFS(C[-37],R[0]C[-37],C[-36],R[0]C[-36],C[-31],R[0]C[-31])"
Range("Am2").Select
ActiveCell.FormulaR1C1 = "= 1/COUNTIFS(C[-38],R[0]C[-38],C[-34],R[0]C[-34],C[-32],R[0]C[-32])"

Range("Am2").Select
.Range("Al2:Am2").AutoFill Destination:=.Range("Al2:Am" & DernLigne)
'.Range("Al2:Am" & DernLigne) = .Range("Al2:Am" & DernLigne).Value
End With

 

[Dranreb]

Bonjour.

ActiveCell.FormulaR1C1 = "=1/COUNTIFS(R2C1:R" & DernLigne & "C1,RC1,R2C5:R" & DernLigne & "C5,RC5,R2C7:R" & DernLigne & "C7,RC7)"

 

[job75]

Bonjour jlbcall, Bernard,

Effectivement limiter les plages fait gagner du temps.

Sur le fichier du post #1 cette macro s'exécute chez moi en 42 secondes :

Sub NbrS1()
Dim t#, derlig&
t = Timer
With Sheets("Data")
    .[AL1] = "SA": .[AM1] = "VE"
    derlig = .UsedRange.Rows.Count
    If derlig = 1 Then Exit Sub
    .[AL2].Resize(derlig - 1) = "=1/COUNTIFS(A$2:A$" & derlig & ",A2,B$2:B$" & derlig & ",B2,G$2:G$" & derlig & ",G2)"
    .[AM2].Resize(derlig - 1) = "=1/COUNTIFS(A$2:A$" & derlig & ",A2,E$2:E$" & derlig & ",E2,G$2:G$" & derlig & ",G2)"
End With
MsgBox "Durée " & Format(Timer - t, "0.00 \s")
End Sub

Alors que celle-ci s'exécute en 114 secondes :

Sub NbrS2()
Dim t#, derlig&
t = Timer
With Sheets("Data")
    .[AL1] = "SA": .[AM1] = "VE"
    derlig = .UsedRange.Rows.Count
    If derlig = 1 Then Exit Sub
    .[AL2].Resize(derlig - 1) = "=1/COUNTIFS(A:A,A2,B:B,B2,G:G,G2)"
    .[AM2].Resize(derlig - 1) = "=1/COUNTIFS(A:A,A2,E:E,E2,G:G,G2)"
End With
MsgBox "Durée " & Format(Timer - t, "0.00 \s")
End Sub

A+

 

[jlbcall]

Merci beaucoup à touts les deux bon semaine