Need help with changing hyperlink addresses

[sspatriots]

I've used similar code like what I have below with much success. However, this time the hyperlinks changed such that they have the "%20" in the address for spaces. I need to get the "%20" replaced with spaces and get the first part of that file path changed to the new string I have specified. When I run the code I get the error "Run-time error '7': Out of Memory at the line that after "Else". I have no clue what I have wrong here. Any help would be much appreciated. Thanks, SS

This post is cross-posted here: https://www.mrexcel.com/board/thread...esses.1210166/

Sub FixPOHyperlinks()

    Dim wBook As Workbook
    Dim wSheet As Worksheet
    Dim tb As ListObject
    Dim OldStr As String, NewStr As String
    Dim hyp As Hyperlink
    Dim sOldAddress As String, sNewAddress As String

    Set wBook = ThisWorkbook
    Set wSheet = wBook.Sheets("Sheet1")
    Set tb = wSheet.ListObjects("Table1")

    Worksheets("Sheet1").Activate

    OldStr = "https://companyname-my.sharepoint.com/personal/mescobal_companyname_com/Documents/H%20drive"
    NewStr = "\\abc.local\DEM"
    
    For Each wSheet In Worksheets
       
        For Each hyp In tb.ListColumns("Machine PO").DataBodyRange.Hyperlinks
    
            If InStr(1, hyp.Address, "\") > 0 Then
                hyp.Address = Replace(hyp.Address, OldStr, NewStr)
                hyp.Address = Replace(hyp.Address, "%20", Chr(32))
            Else
                hyp.Address = NewStr & "\" & hyp.Address
            End If
    
            'hyp.TextToDisplay = Replace(hyp.Address, OldStr, NewStr)

        Next hyp

    Next

End Sub

[davesexcel]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.