Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy hyperlinks
Hi
I have a sheet containing 2-300 hyperlinks to webpages in a row. The row to the left of the row with hyperlinks contains an ID for each link. This is a nice table, and I thought it would be a simple task to use VLOOKUP to put the hyperlinks in to another table that uses the same ID as in the first table with hyperlink. To give you an idea of what I mean, please look at the example tables below: Table 1 Table 2 ID1 Hyperlink1 ID3 ID2 Hyperlink2 ID1 ID3 Hyperlink3 ID5 .. .. .. IDn Hyperlinkn Table 2 should then use VLOOKUP to fetch the hyperlinks from Table 1. The problem is that VLOOKUP does not put in the links as links, only the text that the links show. Example: Lets say a hyperlink shows the text "New York Times", and by clicking on the link it takes you to www.nytimes.com. VLOOKUP then simply puts the text "New York Times in the cell where VLOOKUP is. Same thing happens when using Copy and Paste from Visual Basic. However, doing a manual Copy and Paste in the sheet actually copies the links as links. So if someone knows a way to copy/lookup the links, that actually results in clickable links at the destination, I will be gratefull. Thanks a lot for any help! -- Jesper Kaas - |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy hyperlinks
Hi Jesper,
Am Sun, 05 May 2013 14:11:08 +0200 schrieb Jesper Kaas: I have a sheet containing 2-300 hyperlinks to webpages in a row. The row to the left of the row with hyperlinks contains an ID for each link. This is a nice table, and I thought it would be a simple task to use VLOOKUP to put the hyperlinks in to another table that uses the same ID as in the first table with hyperlink. To give you an idea of what I mean, please look at the example tables below: Table 1 Table 2 ID1 Hyperlink1 ID3 ID2 Hyperlink2 ID1 ID3 Hyperlink3 ID5 modify following code to suit: Sub CopyHyperlinks() Dim rngC As Range Dim c As Range Dim LRow As Long With Sheets("Sheet2") LRow = .Cells(.Rows.Count, "A").End(xlUp).Row For Each rngC In .Range("A1:A" & LRow) Set c = Sheets("Sheet1").Range("A1:A300") _ .Find(rngC, LookIn:=xlValues) If Not c Is Nothing Then c.Offset(0, 1).Copy rngC.Offset(0, 1) End If Next End With End Sub Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy hyperlinks
On Sun, 05 May 2013 14:11:08 +0200, Jesper Kaas
wrote: Hi I have a sheet containing 2-300 hyperlinks to webpages in a row. The row to the left of the row with hyperlinks contains an ID for each link. This is a nice table, and I thought it would be a simple task to use VLOOKUP to put the hyperlinks in to another table that uses the same ID as in the first table with hyperlink. To give you an idea of what I mean, please look at the example tables below: Table 1 Table 2 ID1 Hyperlink1 ID3 ID2 Hyperlink2 ID1 ID3 Hyperlink3 ID5 . . . IDn Hyperlinkn Table 2 should then use VLOOKUP to fetch the hyperlinks from Table 1. The problem is that VLOOKUP does not put in the links as links, only the text that the links show. Example: Lets say a hyperlink shows the text "New York Times", and by clicking on the link it takes you to www.nytimes.com. VLOOKUP then simply puts the text "New York Times in the cell where VLOOKUP is. Same thing happens when using Copy and Paste from Visual Basic. However, doing a manual Copy and Paste in the sheet actually copies the links as links. So if someone knows a way to copy/lookup the links, that actually results in clickable links at the destination, I will be gratefull. Thanks a lot for any help! Take your "=VLOOKUP(yada,yada)" and put "=HYPERLINK(VLOOKUP(yada,yada))" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy hyperlinks
On Sun, 05 May 2013 07:19:18 -0700, CellShocked
<cellshocked@thecellvalueattheendofthespreadsheet. org wrote: On Sun, 05 May 2013 14:11:08 +0200, Jesper Kaas wrote: Hi I have a sheet containing 2-300 hyperlinks to webpages in a row. The row to the left of the row with hyperlinks contains an ID for each link. This is a nice table, and I thought it would be a simple task to use VLOOKUP to put the hyperlinks in to another table that uses the same ID as in the first table with hyperlink. To give you an idea of what I mean, please look at the example tables below: Table 1 Table 2 ID1 Hyperlink1 ID3 ID2 Hyperlink2 ID1 ID3 Hyperlink3 ID5 . . . IDn Hyperlinkn Table 2 should then use VLOOKUP to fetch the hyperlinks from Table 1. The problem is that VLOOKUP does not put in the links as links, only the text that the links show. Example: Lets say a hyperlink shows the text "New York Times", and by clicking on the link it takes you to www.nytimes.com. VLOOKUP then simply puts the text "New York Times in the cell where VLOOKUP is. Same thing happens when using Copy and Paste from Visual Basic. However, doing a manual Copy and Paste in the sheet actually copies the links as links. So if someone knows a way to copy/lookup the links, that actually results in clickable links at the destination, I will be gratefull. Thanks a lot for any help! Take your "=VLOOKUP(yada,yada)" and put "=HYPERLINK(VLOOKUP(yada,yada))" That one works if the original hyperlink-cell contains a webaddress like http://www.nytimes-no. Unfortunatelu it does not work if the original cell contains a clickable text that actually is a hyperlink, like "New York Times" in my example. Thanks anyway. It brings me part of the way. -- Jesper Kaas - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy hyperlinks
On Sun, 05 May 2013 18:44:54 +0200, Jesper Kaas
wrote: On Sun, 05 May 2013 07:19:18 -0700, CellShocked <cellshocked@thecellvalueattheendofthespreadsheet .org wrote: On Sun, 05 May 2013 14:11:08 +0200, Jesper Kaas wrote: Hi I have a sheet containing 2-300 hyperlinks to webpages in a row. The row to the left of the row with hyperlinks contains an ID for each link. This is a nice table, and I thought it would be a simple task to use VLOOKUP to put the hyperlinks in to another table that uses the same ID as in the first table with hyperlink. To give you an idea of what I mean, please look at the example tables below: Table 1 Table 2 ID1 Hyperlink1 ID3 ID2 Hyperlink2 ID1 ID3 Hyperlink3 ID5 . . . IDn Hyperlinkn Table 2 should then use VLOOKUP to fetch the hyperlinks from Table 1. The problem is that VLOOKUP does not put in the links as links, only the text that the links show. Example: Lets say a hyperlink shows the text "New York Times", and by clicking on the link it takes you to www.nytimes.com. VLOOKUP then simply puts the text "New York Times in the cell where VLOOKUP is. Same thing happens when using Copy and Paste from Visual Basic. However, doing a manual Copy and Paste in the sheet actually copies the links as links. So if someone knows a way to copy/lookup the links, that actually results in clickable links at the destination, I will be gratefull. Thanks a lot for any help! Take your "=VLOOKUP(yada,yada)" and put "=HYPERLINK(VLOOKUP(yada,yada))" That one works if the original hyperlink-cell contains a webaddress like http://www.nytimes-no. Unfortunatelu it does not work if the original cell contains a clickable text that actually is a hyperlink, like "New York Times" in my example. Thanks anyway. It brings me part of the way. Yes... My lookups did/do refer to actual existing hyperlink entries within the referenced cell itself. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy hyperlinks
On Sun, 5 May 2013 15:01:38 +0200, Claus Busch
wrote: Hi Jesper, modify following code to suit: Sub CopyHyperlinks() Dim rngC As Range Dim c As Range Dim LRow As Long With Sheets("Sheet2") LRow = .Cells(.Rows.Count, "A").End(xlUp).Row For Each rngC In .Range("A1:A" & LRow) Set c = Sheets("Sheet1").Range("A1:A300") _ .Find(rngC, LookIn:=xlValues) If Not c Is Nothing Then c.Offset(0, 1).Copy rngC.Offset(0, 1) End If Next End With End Sub Regards Claus Busch Hi Claus Thanks a lot! Your code does what I want done. I can't say that i understand the code, but it is easy to modify it to do what I need to do, and look at the rest as a "black box". Best regards -- Jesper Kaas - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy hyperlinks
Hi Jesper,
Am Sun, 05 May 2013 19:01:43 +0200 schrieb Jesper Kaas: I can't say that i understand the code, but it is easy to modify it to do what I need to do, and look at the rest as a "black box". if you can modify the code you understand the code ;-) Thank you for the feedback Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste the hyperlinks | Excel Discussion (Misc queries) | |||
Hyperlinks: Hyperlinks change on copy/paste? | Excel Worksheet Functions | |||
Copy HYPERLINKS | New Users to Excel | |||
Copy/Paste using hyperlinks | Excel Programming | |||
how do you copy hyperlinks from one worksheet to another | Excel Worksheet Functions |