Automatic Hyperlink between two rows based on matching content in field

Brian Marshall

New Member
Joined
Aug 24, 2010
Messages
6
I have what would seem to me to be a simple request, unfortunately not for me. :eek:

I would like to write a VB script that will take the Value in Column A and find its match in Column B in the same excel sheet and create a dynamic hyperlink to it. I have 100s of values in Col A sorted in an order that matches a document and Column B is randomly ordered and moves around based on other data, but the values are unique. I am running Excel 2007 and would appreciate any help.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

MikeLiberty

Board Regular
Joined
Aug 13, 2010
Messages
55
I have what would seem to me to be a simple request, unfortunately not for me. :eek:

I would like to write a VB script that will take the Value in Column A and find its match in Column B in the same excel sheet and create a dynamic hyperlink to it. I have 100s of values in Col A sorted in an order that matches a document and Column B is randomly ordered and moves around based on other data, but the values are unique. I am running Excel 2007 and would appreciate any help.

Something like this might get you started
Dim FoundCell as Range

' To find your unique value in column B
Columns("B:B").Select
Selection.Find(What:=Range("A1").Value, After:=Range("B1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

' Get the address of the cell you found
FoundCell = ActiveCell.Address

'Add a Hyperlink to your cell in column A
ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), Address:="", SubAddress:= "Sheet1!" & FoundCell


Then you can put that in a loop to work through each cell in Column A
 

MikeLiberty

Board Regular
Joined
Aug 13, 2010
Messages
55
The code above assumes that there is always a matching value for column A in column B. With some playing around, you could build an if statement to handle that.

It also assumes that the values in column B are unique (as you mentioned).
 

Brian Marshall

New Member
Joined
Aug 24, 2010
Messages
6
Well it is getting closer. I think its finding the cell but then the FoundCell=Activate.address gets a run-time error 91.. I can in fact find the value in cell A1 in the B column using find.... I assume the activate in the previous command was designed to activate the found cell and if not found would cause this bug.. But it is there.....
 

MikeLiberty

Board Regular
Joined
Aug 13, 2010
Messages
55

ADVERTISEMENT

It should be FoundCell=ActiveCell.Address, not Activate.Address.

The activate in the find statement does make that cell the active one. I think you can also combine the two statements so that

FoundCell= Selection.Find(What:=Range("A1").Value, After:=Range("B1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Address

I just split it out so you could see the steps.
 

Brian Marshall

New Member
Joined
Aug 24, 2010
Messages
6
Sorry about that. I did have the FoundCell =. Question the VB script you sent does it look at the value of the cell in A1 or the formula? It is value that I am trying to match... Here is what I currently have and it still gets the variable not set message. TIA. I do appreciate the help.


Sub markhyper()
Dim FoundCell As Range
' To find your unique value in column B
Columns("B:B").Select
FoundCell = Selection.Find(What:=Range("A6").Value, After:=Range("B1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Address
' Get the address of the cell you found
'FoundCell = ActiveCell.Address
'Add a Hyperlink to your cell in column A
ActiveSheet.Hyperlinks.Add Anchor:=Range("A6"), Address:="", SubAddress:="Findings!" & FoundCell

End Sub
 

Brian Marshall

New Member
Joined
Aug 24, 2010
Messages
6

ADVERTISEMENT

I got it. Thanks for the help.. the as Range in the DIM was causing the error. Here is the code that worked for me.

Sub markhyper()
Dim FoundCell
' To find your unique value in column B
Columns("B:B").Select
FoundCell = Selection.Find(What:=Range("A6").Value, After:=Range("B1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Address
' Get the address of the cell you found
' FoundCell = ActiveCell.Address
'Add a Hyperlink to your cell in column A
ActiveSheet.Hyperlinks.Add Anchor:=Range("A6"), Address:="", SubAddress:="Findings!" & FoundCell

End Sub
 

Brian Marshall

New Member
Joined
Aug 24, 2010
Messages
6
Okay, so my only question now is how can I address the RANGE("A").value such that I can increment i by 1 looping through the column.

It would seem that excel does not like the notation "A". i is my for loop incrementor.. I did try and rewirte used the for each c in.. But that got a bit ugly.. .If I cannot increment i this way, I guess I will need to rewrite a bit.
 

Brian Marshall

New Member
Joined
Aug 24, 2010
Messages
6
Well I answered my own question this time. It tuns out that since excel views the address as a string in the quotes, I was able to concatenate it with a variable.. Here is my final working code. Thanks for the Help...

Sub markhyper()
Dim FoundCell
' To find your unique value in column B
Dim i As Integer
For i = 6 To 56
Columns("B:B").Select

FoundCell = Selection.Find(What:=Range("A" & CStr(i)).Value, After:=Range("B1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Address
' Get the address of the cell you found
' FoundCell = ActiveCell.Address
'Add a Hyperlink to your cell in column A
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & CStr(i)), Address:="", SubAddress:="Findings!" & FoundCell
Next
End Sub
 

MikeLiberty

Board Regular
Joined
Aug 13, 2010
Messages
55
Strange that it was working for me as a range but not for you. Still learning the quirks of Excel VBA myself. Glad you found your solution and thanks for posting it so I could see what worked.
 

Forum statistics

Threads
1,136,434
Messages
5,675,841
Members
419,586
Latest member
RoteichA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top