Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'valeur dans la colonne des Nom Copro
If Not Intersect(Columns(4), Target) Is Nothing Then
Application.EnableEvents = False
If Target = "INDIV" Then
'aucune formule en colonnes
Range(Cells(Target.Row, 5), Cells(Target.Row, 10)) = ""
Else
Formules_Recherche Target.Row
End If
End If
Application.EnableEvents = True
End Sub
Private Sub Formules_Recherche(numlig As Long)
'Ecrit les formules de recherche pour la ligne concernée passée en paramètre
Range("E" & numlig).FormulaR1C1 = "=IF([@[Nom Corpro]]<>""INDIV"",IFERROR(VLOOKUP([@[Nom Corpro]],mes_copros,2,FALSE),""""),"""")"
Range("F" & numlig).FormulaR1C1 = "=IFERROR(VLOOKUP([@[Nom Corpro]],mes_copros,3,FALSE),"""")"
Range("G" & numlig).FormulaR1C1 = "=IFERROR(VLOOKUP([@[Nom Corpro]],mes_copros,4,FALSE),"""")"
Range("H" & numlig).FormulaR1C1 = "=IFERROR(VLOOKUP([@[Nom Corpro]],mes_copros,5,FALSE),"""")"
Range("I" & numlig).FormulaR1C1 = "=IFERROR(VLOOKUP([@[Nom Corpro]],mes_copros,6,FALSE),"""")"
Range("J" & numlig).FormulaR1C1 = "=IFERROR(VLOOKUP([@[Nom Corpro]],mes_copros,7,FALSE),"""")"
End Sub