Macro to replace part of hyperlink

[snoteborn]

I need some help with the following issue.

I'm having the following situation:

Cell A1 contains three digits: 111
Cell A4, A5, A6, A7 all contain the following example link: www.test.com/555.html/9990-451

My probelm is that I want to create a macro which changes a part (the 555) of this link into the three 111 of cell A1.

I have to use this macro for about 50 cells and the same for 50 sheets. This is why I really need a macro because doing it manually is quite a job.

My main goal is to have a button which easily copy's the value of one cell into a part of a lot of other cells, without changing anything else in the links.

Many thanks if someone can offer me a solution!

[Excelenator]

This is a stab in the dark because I'm not certain of your setup, but based on how you laid out your example, if you select the cell that has the "111" in it ("A1" in your example) this code will replace the characters in the links between the "/" and the ".html" in cells 3, 4, and 5 rows below the selected cell ("A4:A6" in your example).

Sub ReplaceLinks()
    Dim lnk As String, rpl As String
    Dim i As Integer
    
    rpl = Selection.Value
    
    For i = 3 To 5
        lnk = Selection.Offset(i, 0).Value
        lnk = Mid(lnk, 1, InStr(1, lnk, "/")) & rpl & Right(lnk, Len(lnk) - InStrRev(lnk, ".html") + 1)
        Selection.Offset(i, 0).FormulaR1C1 = lnk
    Next i
End Sub