2

I have a spreadsheet where column A displays names. There are a few hundred names and each has a different hyperlink (which links to that person's web page). I want to transfer those hyperlinks across to a different column which has different text in and no hyperlinks.

Not every cell in column A has a hyperlink. There are groups of cells merged together, so A2:A7 has one link, A8:A13 the next, A9:10 the next (i.e. number of cells merged is not uniform).

e.g. where A2:A7 reads "Bob" and links to www.bob.com, I want I2:I7, which reads, "Smith," and does not link to anything, to link to www.bob.com. I want to do this repeatedly, copying links from A2:A579 into I2:I579.

The information is copied from a table within a web page, and that is where the hyperlinks come from.

OfficeLackey
  • 25
  • 2
    Here is an important piece of information you've left out of your question; How are the existing hyperlinks created in the cells? Do they use the HYPERLINK function or are they embedded using Insert Hyperlink from the Insert ribbon? – CharlieRB Jun 04 '14 at 15:42
  • This question is so unclear... Have the information is in comments. Why explain the order was incorrect if you've fixed it, it just adds waffle to your question... Please click here to edit your question and explain exactly how the information is populated in your Excel document. As the question is now, IMO it's unclear what you want and I have voted to close the question – Dave Jun 05 '14 at 10:27
  • @DaveRook I'm sorry, this is the first time I have used this forum and I didn't think to edit the question rather than comment. I'm doing my best to present the information such that people can help me. Hope this is better. Please tell me what you need to know if I haven't expressed myself clearly enough still. – OfficeLackey Jun 05 '14 at 13:35
  • Right, your question is now clear. However, I suspect you will want to do this multiple times? If that is the case, then what is the logic? Do you always want to remove A1 hyperlink and replace it with A10 hyperlink? And then again with B1 and B10, C1 and C10 etc? – Dave Jun 05 '14 at 13:54
  • @DaveRook Please see updated question. – OfficeLackey Jun 05 '14 at 13:59
  • @Madball73 I had already looked at that question and it is not the same as mine - I have since revised the phrasing of my question to clarify – OfficeLackey Jun 05 '14 at 14:36
  • @CharlieRB See edit – OfficeLackey Jun 05 '14 at 14:37
  • The tagged duplicate question is asking for an excel formula. Here is an answer to this original question:
    1. select and copy cells with hyperlinked text
    2. open Outlook and paste text into draft email
    3. change formatting from "HTML" to plain text
    4. see text links convert to full URLs
    5. copy these back into Excel
     – blakemade Apr 10 '20 at 22:02

1 Answers1

1

This macro will help

Sub SwapIt()

For i = 2 To 579

If Range("A" & i).Value <> "" And Range("I" & i).Value <> "" Then

    Dim newLink As String ' the new link string needs a place to live... just like me!

    If Range("A" & i).Hyperlinks.Count = 1 Then
    newLink = Range("A" & i).Hyperlinks(1).Address ' Get the old horrible link :)
    Range("I" & i).Hyperlinks.Add anchor:=Range("I" & i), Address:=Range("I" & i) ' horrible hack, just to get it to a link
    Range("I" & i).Hyperlinks(1).Address = newLink '' replace with the new link... Much better. Like a ray of sunshine
    End If
End If

Next i

End Sub

This will move the hyperlink From A2 to I2, then A3 to I3 etc

Dave
  • 25,405
  • Updated the code, this will now do from row 2 to 579 – Dave Jun 05 '14 at 14:06
  • I am getting: Run time error 9 - subscript out of range. – OfficeLackey Jun 05 '14 at 14:11
  • Range("I" & i).Hyperlinks(1).Address = newLink – OfficeLackey Jun 05 '14 at 14:14
  • The I column does not have any hyperlinks originally (which I have stated in question edit) - does this make a difference to your code? – OfficeLackey Jun 05 '14 at 14:16
  • No, my original question said each has different text and that I want to move the hyperlinks from A to I. I realised that I had not explicitly stated there were no hyperlinks in I, and updated question appropriately. Sorry if I am being unhelpful - I am doing my best to explain my problem succinctly! – OfficeLackey Jun 05 '14 at 14:28
  • Updated - I've had to hack it in due to time restraints, but, I will still work – Dave Jun 05 '14 at 14:42
  • Now getting same Run time error on: newLink = Range("A" & i).Hyperlinks(1).Address - sorry to be a pain again =S – OfficeLackey Jun 05 '14 at 14:47
  • Ah! No. That would be the issue. Will edit question. Sorry. – OfficeLackey Jun 05 '14 at 14:56
  • Updated again for you. OfficeLackey, if you're having to edit your question this often, you may need to consider asking a new question. You can ask many questions. The idea isn't to ask 1 question, and keep updating the question when you realise something isn't quite right etc – Dave Jun 05 '14 at 15:01
  • See edit - I am aware this is rather complicated and I'm probably not helping. Very grateful for your time. – OfficeLackey Jun 05 '14 at 15:02
  • Can you confirm if it works or not. – Dave Jun 05 '14 at 15:46