Fix Hyperlinks

[sspatriots]

Good evening,

I found the code below online that is supposed to change the hyperlink file paths to a selected range. Every time I use it, only the cell I select prior to selecting a range gets updated. Just trying to figure out what I'm missing here.

Sub ChangeHyperlinks()

Dim cell As Range

For Each cell In Selection

If cell.Hyperlinks.Count > 0 Then

cell.Hyperlinks(1).Address = Replace(cell.Hyperlinks(1).Address, "..\..\drives\rManufacturing\00-Commercial & Group 2 PO Archive\2023", "H:\Jobs\00 PO ARCHIVE\2023")

End If

Next cell

End Sub

 

[Kerryx]

Did you just select a single cell rather than all the full range of cells you want checked

 

[sspatriots]

Yes and yet only the one cell’s hyperlink gets updated.

 

[RoryA]

If you only select one cell, then there is only one cell in the Selection, so that is all that gets processed.

 

[sspatriots]

No. I selected an entire range before running the code. That why I’m posting the question.

 

[RoryA]

Then why did you just say you only selected one cell?

 

[sspatriots]

Then why did you just say you only selected one cell?

Only one cell got updated which was the one that was selected prior to selecting the range. I think I explained it that way in my original post. It says prior to selecting a range.

 

[RoryA]

Your code will process every cell in the current selection. If it's only processing one cell, then that is all that is selected at the time the code runs. How are you running this code?

 

[Georgiboy]

@RoryA To be fair, I just set up a block of links with the specific link text from the above posts I also added the TextToDisplay name to see what was going on, and:

Only the first two links changed (with the TextToDisplay) BUT: all of the links changed before it got to the end of the loop, the links changed within cells that had not been looped over yet. It seems as if Excel is flash filling the links that it assumes need to be changed, this seems to stop the loop functioning as the oldLink text is not found anymore.

Maybe see if the attached does the same for you: HLinks.xlsm

 

[RoryA]

Yes, I see the same. That's a little worrying frankly (especially as I have the auto flash fill option turned off!).