Automatic Hyperlink between two rows based on matching content in field

[Brian Marshall]

I have what would seem to me to be a simple request, unfortunately not for me.

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.

 

[MikeLiberty]

I have what would seem to me to be a simple request, unfortunately not for me.

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]

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]

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]

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]

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]

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]

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]

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]

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.