PDA

View Full Version : [SOLVED] HELP -- Hyperlink Question



sanluisr
04-14-2015, 11:28 AM
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
04-14-2015, 10:06 PM
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
04-15-2015, 08:06 AM
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
04-15-2015, 09:19 AM
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
04-15-2015, 10:33 AM
Hey Yongle,

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

Please see actual sample data attached.

13184

Yongle
04-15-2015, 11:33 PM
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
04-16-2015, 03:11 AM
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
04-16-2015, 04:49 AM
:thumb@snb Neat!!

sanluisr
04-16-2015, 08:21 AM
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
04-16-2015, 09:20 AM
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
04-16-2015, 10:00 AM
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
04-16-2015, 10:35 AM
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
04-16-2015, 10:52 AM
Hi Youngle -- I copied exactly the codes from your previous entry.

Yongle
04-16-2015, 11:59 AM
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
04-16-2015, 12:32 PM
Hi Yongle -- please see attached test file.
13192

Yongle
04-16-2015, 02:43 PM
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
04-16-2015, 03:11 PM
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
04-16-2015, 10:33 PM
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
04-17-2015, 08:16 AM
You got it, Yon! And thanks again ... until next time :hi: