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