HELP -- Hyperlink Question

[sanluisr]

Hello ~

Please help ...

I have over 200 rows of ID numbers in Column A and a list of URLs in Column B. Is there a VBA code to link each URL to the job req? Any help is greatly appreciated

[Yongle]

Welcome to the forum Sanluisr

You have not told us what job req is - is it in another workbook, another worksheet or is it the ID in colA?
What do you want to vba to achieve? Presumably you want to click on one cell to take you somewhere else?
thanks

[sanluisr]

Hi Yongle,

You are correct! So each ID number is actually a job opening and, when "clicked", should take you to an internal website that has the job description.

Currently, I'm having to do the hyperlink manually for each job ID number (for over 200 rows). I've seen the VBA codes to show all hyperlinks ... now I'm trying to do the reverse.

Hope this clarifies it.

[Yongle]

Test this in the attached workbook. Run it from sheet1.
It does everything within the active sheet
Easy to modify if you want the links placed elsewhere

what it does
It creates hyperlinks in Column A
It leaves the values in column A (ie the job ID)
It uses the hyperlinks already set up in column B
(which in the test link to the other 3 sheets)

I have included a line to delete column B afterwards - to use it, remove the leading apostrophe

Sub ID_HyperLinks()


Dim LastRow As Long, i As Long
Dim Rng As Range, RngSub As Range


LastRow = ActiveSheet.Range("A2").End(xlDown).Row
  
For i = 2 To LastRow
    Set Rng = ActiveSheet.Cells(i, 1)
    Set RngSub = ActiveSheet.Cells(i, 2)
    ActiveSheet.Hyperlinks.Add Anchor:=Rng, Address:="", SubAddress:=RngSub.Value, TextToDisplay:=Rng.Value
Next i


'ActiveSheet.Columns("B").EntireColumn.Clear


End Sub

[sanluisr]

Hey Yongle,

It does work on your template ... however, it didn't work on mine

Please see actual sample data attached.

Test File.xlsx

[Yongle]

This will work on your data
(cells in my colB contained hyperlinks, whereas yours contained text)

This macro creates a hyperlink from the text in columnB in the active sheet
(I have added a check that it contains text)
and then copies the ID from colA to ColB, replacing the URL text with ID
So after macro is run clear column A (remove the leading apostrophe to activate line in vba)
Test this in a copy of your file because this overwrites ColB values

Sub ID_HyperLinks()

Dim LastRow As Long, i As Long
Dim RngSub As Range
LastRow = ActiveSheet.Range("A2").End(xlDown).Row
For i = 2 To LastRow
     
    Set RngSub = ActiveSheet.Cells(i, 2)
       
        If RngSub <> "" Then
            ActiveSheet.Hyperlinks.Add RngSub, RngSub.Value
            Cells(i, 2).Value = Cells(i, 1).Value
        End If
Next i
'ActiveSheet.Columns("A").EntireColumn.Clear
End Sub

[snb]

Sub M_snb() 
  for each cl in activesheet.columns(1).specialcells(2)
     ActiveSheet.Hyperlinks.Add cl, cl.Value 
     cl.offset(,1)= cl.Value 
  Next 
End Sub

[Yongle]

@snb Neat!!

[sanluisr]

Hi snb & Yongle,

Thanks for the reply ... but I must be doing something wrong The URL address becomes the job number so the link does not work

[Yongle]

No you did nothing wrong. I did.
I should have checked behind the link - it looked ok on the worksheet but the link was overwritten by the ID values .
My sincerest apologies for not checking properly.

Sub ID_HyperLinks()


Dim LastRow As Long, i As Long
Dim RngSub As Range
Dim Str As String
LastRow = ActiveSheet.Range("A2").End(xlDown).Row
For i = 2 To LastRow
    
    Set RngSub = ActiveSheet.Cells(i, 2)
    Str = ActiveSheet.Cells(i, 1).Value
        If RngSub <> "" Then
          ActiveSheet.Hyperlinks.Add RngSub, RngSub.Value, TextToDisplay:=Str
        End If
Next i
End Sub

[sanluisr]

No worries Youngle .. and I greatly I appreciate your help (you both)!

I think we're almost there When I ran the codes, the job ID remains in Column A (with no link). However, there are active hyperlinks in Column B but with invisible job numbers.

[Yongle]

Did you copy the whole of the code in or just amend?
It looks as though variable str has no value

What drives the value of TextToDisplay is "Str"
- whose value comes from line Str = ActiveSheet.Cells(i, 1).Value
- and is declared as a variable Dim Str As String

[sanluisr]

Hi Youngle -- I copied exactly the codes from your previous entry.

[Yongle]

Most peculiar

Can you add this line immediately above End if

MsgBox "Cell = " & Cells(i, 2).Value & vbNewLine & " String = " & Str

You should find that the message box gives you 2 values (for cell and for string) and they should both be the ID


If they are not, please post your workbook

[sanluisr]

Hi Yongle -- please see attached test file.
Hyperlink Test File.xlsx

[Yongle]

I have taken your file and popped in the macro and it worked first time.

The workbook is attached with the macro included
Run the macro
You will find column B has the stock ID with the links attached

I would like you to test it a couple of times
(you will need to paste the values in duplicate column E over the top of column B, highlight column B, right click and remove hyperlinks and run it again)

Having proven it works, to your satisfaction, next paste your sample data into into Sheet 2 and run the macro from that sheet

If you get a different result we will know where to look!

thanks

[sanluisr]

OMG, Yongle! You did it! What a rockstar!? I did exactly what you suggested on tab 2 with all 200 lines -- and it worked! It does ask me to click "ok" for each line but I'm perfectly ok with that.

Again, I greatly appreciate it

Best,
Robert

[Yongle]

Hi Robert

You are most welcome.
Message boxes can be a useful way to watch what is going on, particularly so if you are not sure why something is not doing what you want.
Now that everything is doing what you want, delete the line starting with MsgBox
Can you click on thread tools (top of thread) and mark the thread as "solved"
thanks
Yon

[sanluisr]

You got it, Yon! And thanks again ... until next time