Macro to replace part of hyperlink (New Request)

Good Day,

I have a series of spreadsheets with hyperlinks to PDF files. I am changing the directory of the PDFs and want to update a portion of the hyperlinks from "\\SharedDrive\PublicFolder\AnotherFolderA\AnotherFolderA\2011\CC-11-280.pdf" to "\\SharedDrive\PublicFolder\AnotherFolderB\AnotherFolderB\2011\CC-11-280.pdf"

There is a thread (called Macro to replace part of hyperlink) which had the following VBA code:

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

I modified it and ran it but it also add the updated hyperlink text to the existing displayed text. (CC-11-280 became AnotherFolderB\AnotherFolderBCC-11-280)

I need to update all of the links in a column where some of the links have been updated and several have not.

Please help.

That did not work. It will only allow it in Formulas.