Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 8 Then Exit Sub 'si pas colonne H, on sort
If (Target.Row - 7) Mod 12 <> 0 Then Exit Sub 'si pas lignes 7, 19, 31, 43, etc.
Cancel = True
Application.ScreenUpdating = False
Target = 1
Target.Offset(1, 0).Resize(11, 1).FormulaR1C1 = "=if(RC[1]="""","""",R[-1]C+1)" 'on inscrit les SI... dans les 11 lignes de la colonne H
With Target.Offset(0, -3).Hyperlinks(1)
fich = Replace(Mid(.Address, 1, InStrRev(.Address, "\", -1, vbTextCompare)) & "[" & Mid(.Address, InStrRev(.Address, "\", -1, vbTextCompare) + 1, 50) & "]", "..\..\", "Z:\Qualite\")
End With
premLigne = Target.Row
For c = 1 To 4
Select Case c
'attribuer une lettre de colonne et un n° de ligne de départ pour chaque formule des colonnes I à L
Case 1
col = "A"
ligDep = 6
Case 2
col = "Q"
ligDep = 9
Case 3
col = "V"
ligDep = 7
Case Else
col = "V"
ligDep = 9
End Select
For lig = 0 To 11
'on écrit les formules
Cells(premLigne + lig, 8 + c).Formula = "=if('" & fich & "FAD'!" & col & ligDep + 5 * lig & "=0,"""",'" & fich & "FAD'!" & col & ligDep + 5 * lig & ")"
Next lig
Next c
Application.ScreenUpdating = True
End Sub