changing a wildcard in an excel hyperlink with vba

Question

I would like some help with removing/replacing a wildcard character in an excel hyperlink. Logically it seems very easy but it's beyond my abilities.

I have a spreadsheet with hyperlinks to PDF documents. The hyperlinks contain the "#" character and that stops the file path from working. In the hyperlink I simply need to change the "#" to "%23" and the link works. I don't want to do this manually because of the amount of links. Is there any way of achieving this with VBA. It seems easy enough to change a file path but searching a hyperlink and changing the # doesn't seem to be possible.

All the hyperlinks are in column A.

Answer 1

Excel treats text to the left of the # as the .Address and to the right as the .SubAddress - as it suggests an anchor type link. You'd need to repair this on each link like so:

For Each lk In Sheets("YourSheetName").Range("A:A").Hyperlinks
    If lk.SubAddress <> "" Then
        lk.Address = lk.Address & "%23" & lk.SubAddress
        lk.SubAddress = ""
    End If
Next