Hi. I am no VBA expert, so have had to rely on what I have found by Googling. But I am still getting errors. Can anyone kindly help?



Macros are enabled and the file saved with an xlsm extension. I first created a new module with the script A, below.

Then I created my macro, using a second module window (Alt+F11) which after saving looks like text B: but when I run it I get
Can anyone advise what is wrong? Many thanks!

The original is here (and I replied, but it's an old thread so am probably not going to get answers by this stage):

http://superuser.com/questions/65735...in-excel-cells

Martin

A:

Public Sub ReplaceHyperlinkURL(FindString As String, ReplaceString As String) Dim LinkURL, PreStr, PostStr, NewURL As String Dim FindPos, ReplaceLen, URLLen As Integer Dim MyDoc As Worksheet Dim MyCell As Range On Error GoTo ErrHandler Set MyDoc = ActiveSheet For Each MyCell In MyDoc.UsedRange If MyCell.Hyperlinks.Count > 0 Then LinkURL = MyCell(1).Hyperlinks(1).Address FindPos = InStr(1, LinkURL, FindString) If FindPos > 0 Then 'If FindString is found ReplaceLen = Len(FindString) URLLen = Len(LinkURL) PreStr = Mid(LinkURL, 1, FindPos - 1) PostStr = Mid(LinkURL, FindPos + ReplaceLen, URLLen) NewURL = PreStr & ReplaceString & PostStr MyCell(1).Hyperlinks(1).Address = NewURL 'Change the URL End If End If Next MyCell Exit Sub ErrHandler: MsgBox ("ReplaceHyperlinkURL error") End Sub