3

I have an excel spreadheet that looks like this:

Input: 

Link Name   Hyperlink   Other Data
Group 1     google.de/1     1
Group 1     google.de/2     2
Group 2     google.de/3     3
Group 2     google.de/4     4
Group 3     google.de/5     5

I need a VBA sub that assigns all strings in Link Name as the name to the link in Hyperlink:

Desired output:

Link Name   Hyperlink   Other Data
Group 1     Group 1         1            # Hyperlink: Underlined, blue
Group 1     Group 1         2            # Hyperlink: Underlined, blue
Group 2     Group 2         3            # Hyperlink: Underlined, blue
Group 2     Group 2         4            # Hyperlink: Underlined, blue
Group 3     Group 3         5            # Hyperlink: Underlined, blue

The Hyperlink should now be clickable and labeled as defined in Link Name. My current approach doesn't seem to loop through the Link Name Column:

Current approach:

Sub rename_links()
For Each h In ActiveSheet.Hyperlinks   // Select hyperlink
    h.TextToDisplay = _
    Replace(h.TextToDisplay, h.TextToDisplay, ActiveCell.Offset(0, -1).Value) 
    Next
End Sub

Current output:

Link Name   Hyperlink   Other Data
Group 1     Link Name           1          
Group 1     Link Name           2           
Group 2     Link Name           3            
Group 2     Link Name           4           
Group 3     Link Name           5

I blieve that this is due to ActiveCell.Offset(0, -1).Value not being affected by Next.

How to assign a string in column 1 as name to a hyperlink in column 2 across all rows in excel VBA?

sudonym
  • 3,788
  • 4
  • 36
  • 61
  • wow, anonymous downvote and close flag after ~10 secs – sudonym Apr 16 '18 at 05:25
  • 3
    I’m not the downvoter but I will (and many others are likely to follow) if you don’t provide your coding efforts – DisplayName Apr 16 '18 at 05:27
  • my apologies, I did not think that this may add value - I have added my code now – sudonym Apr 16 '18 at 06:01
  • Why VBA? You can use the formula `=HYPERLINK(A1,B1)` and drag it down? – Siddharth Rout Apr 16 '18 at 06:23
  • VBA is a requirement because this involves python which calls a VBA script at the end of a long pipeline in order to graphically edit the excel-based output – sudonym Apr 16 '18 at 06:24
  • 2
    @sudonym then using VBA to write that formula might be an option – Pᴇʜ Apr 16 '18 at 06:25
  • `Range("D1:D100").Formula = "=HYPERLINK(A1,B1)"` Assuming that you want the links in Col D – Siddharth Rout Apr 16 '18 at 06:26
  • 1
    or `Range("D1:D" & LastRow).Formula = "=HYPERLINK(A1,B1)"` You can find the last row using [THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) – Siddharth Rout Apr 16 '18 at 06:28
  • You can get more help here on adding hyper links in excel: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/hyperlinks-add-method-excel – MD5 Apr 16 '18 at 06:29

1 Answers1

3

You can use a simple formula in column 2 to achieve this:

=HYPERLINK("your link address here", A1)

Which uses the text of A1 as link name.

Or use VBA to write that formula:

Range("B1:B10").Formula = "=HYPERLINK(""your link address here"", A1)"
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • ++ Exactly my thoughts :) – Siddharth Rout Apr 16 '18 at 06:24
  • this works in a static environment. I have accepted the answer. However, an ideal solution does not rely on hardcoded A1 and A2, but IMHO searches for the location of the relevant column names – sudonym Apr 16 '18 at 08:45
  • 1
    @sudonym Well, of course this is only an example showing the relevant technique and no full solution to any *specific* issue. Nevertheless you can only get what you ask for and "*find the relevant column names*" was not part of your question. – Pᴇʜ Apr 16 '18 at 08:59
  • nicely phrased! – sudonym Apr 16 '18 at 09:31