Dim LiensDict As New Collection
Sub InitialiserCollection()
' Ajoutez vos liens et leurs correspondances ici
Set LiensDict = Nothing
LiensDict.Add "titi", key:="D13"
LiensDict.Add "riri", key:="D14"
LiensDict.Add "fifi", key:="D15"
LiensDict.Add "loulou", key:="D16"
LiensDict.Add "toto", key:="D17"
LiensDict.Add "filou", key:="D18"
LiensDict.Add "tata", key:="D19"
End Sub
Function getvalCel(nom_cellule As Range) As String
InitialiserCollection
' Renvoyez la valeur de la cellule
Range("Ref_Cel").Value = LiensDict.Item(CStr(nom_cellule.Address(0, 0)))
End Function
Sub CreerFormules()
Dim ws As Worksheet
Dim cell As Range
Dim key As Variant
Set ws = ThisWorkbook.Sheets("Feuil1")
For Each cell In ws.Range("D13:D" & ws.Cells(LiensDict.Count, "D").End(xlUp).Row + (12 + LiensDict.Count - 1))
key = " " & LiensDict.Item(CStr(cell.Address(0, 0)))
cell.Formula = "=SIERREUR(LIEN_HYPERTEXTE(getvalCel(" & cell.Address(0, 0) & "), ""Cliquez ici"") & """ & key & """, A1 & """ & key & """)"
Next cell
End Sub