VBA substitute link

[jakobt]

Want to write VBA code for:

If in column B, the cell include: "John" or "Mike"
then in column C:
Column C: includes link to other workbooks.
- The link should be updated with the following substituting:
- October substituted wih November:
and
-Actuals substituted with Forecast

Example of link:
h:/Germany/October/Reporting.xlsx.sheet_actuals!A1
(some of the links can have different names but the same principle for substitution should apply)

 

[Yongle]

test in a COPY of your workbook

Sub SwapLinks()
Dim Cel As Range, addr As String

For Each Cel In Range("C2", Range("C" & Rows.Count).End(xlUp))
    If Cel.Offset(, -1) = "John" Or Cel.Offset(, -1) = "Mike" Then
        On Error Resume Next
            addr = Cel.Hyperlinks(1).Address
        If Err.Number = 0 Then
            addr = Replace(addr, "_actuals!", "_forecast!")
            addr = Replace(addr, "October", "November")
            With Cel.Hyperlinks(1)
                .Address = addr
                .TextToDisplay = addr
            End With
        End If
        On Error GoTo 0
    End If
Next