New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Problem with # in hyperlink copy macro

Macros, VBA, Excel Automation, etc.

Problem with # in hyperlink copy macro

Postby moirsky » Tue Jun 07, 2016 4:43 am

Have been already solved:

Had to rework following:

newLink = Range("B" & i).Hyperlinks(1).Address + "#" + Range("B" & i).Hyperlinks(1).SubAddress



Hello guys,

I have made a macro that is copying over hyperlinks from one column to another.

Macro works just fine however hyperlinks contain '#' in the middle of them so lets say:

Code: Select all
https://website.com/nu#somethingsomething


which makes the macro copy just first part of it i.e.:

Code: Select all
https://website.com/nu


Any ideas how to make Excel ignore '#' (if possible at all) and copy over entire value of hyperlink?

VBA code:

Code: Select all
Sub MilestonesHyperlink()
 
For i = 2 To 62 'Number of rows
 
If Range("B" & i).Value <> "" And Range("A" & i).Value <> "" Then 'Copy from column B to A
 
    Dim newLink As String
   
    If Range("B" & i).Hyperlinks.Count = 1 Then
    newLink = Range("B" & i).Hyperlinks(1).Address
    Range("A" & i).Hyperlinks.Add anchor:=Range("A" & i), Address:=Range("A" & i)
    Range("A" & i).Hyperlinks(1).Address = newLink
    End If
End If
 
Next i

End Sub


Thanks a lot.
  • 0

moirsky
Rookie
 
Posts: 1
Joined: Jun 7, 2016
Reputation: 0
Excel Version: 2010

Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google [Bot] and 25 guests

cron