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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
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).
 
Upvote 0
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.....
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top