Replacing hyperlinks with find and Replace

[Abubakr]

Hi all,

Hope everyone is doing fine in everyday life,

I am having problem with a macro that I have recorded, it copies updated Link using Find and replace function but the vba code have harcoded the link, it can only be used once then it is useless, Updated link is in a cell as text.

I am unable to search up any solution for this problem I need urgent help ! Problem is highlighted in the code below.

Sub Test1()
 
 Range("H1").Select
 Selection.Copy
 Range("H2").Select
 Application.CutCopyMode = False
 Selection.Copy
 Range("B4:V5").Select
 Range(Selection, Selection.End(xlDown)).Select
 Selection.Replace What:= _
        "C:\Users\AB-Uc\Desktop\[Landing cost 31-Dec-2022.xlsx]Sheet1'!$F$7" _
        , Replacement:= _
        "C:\Users\AB-Uc\Desktop\Junk\[Budgeted sale.xlsx]Summary'!$A$21" _
        , LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
        :=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

[Bob Phillips]

Something like

Sub Test1()
Dim target As String
Dim replacement As String

    target = Range("A1").Value
    replacement = Range("A2").Value
    
    Range("B4:V5").Select
    Range(Selection, Selection.End(xlDown)).Replace What:=target, _
                      replacement:=Replacement, _
                      LookAt:=xlPart, _
                      SearchOrder:=xlByRows, _
                      MatchCase:=False, _
                      SearchFormat:=False, _
                      ReplaceFormat:=False, _
                      FormulaVersion:=xlReplaceFormula2
End Sub

[arnelgp]

since you are dealing with Hyperlink, i think it is only right to look into all Hyperlinks?

Dim lnk As Hyperlink
For Each lnk In Worksheets(1).Hyperlinks
    With lnk
        'Debug.Print .Address
        If .Address = "C:\Users\AB-Uc\Desktop\[Landing cost 31-Dec-2022.xlsx]Sheet1'!$F$7" Then
            .Address = "C:\Users\AB-Uc\Desktop\Junk\[Budgeted sale.xlsx]Summary'!$A$21"
        End If
    End With
Next