Possible?Find/Replace duplicate cell contents with hyperlink

zoso

zoso

Well-known Member
Joined
Oct 23, 2003
Messages
725
I really hope you can help me with this one!

I have literally hundreds of text entries in a worksheet called Analysis, in column E.

The same text entries also appear scattered within column D of another worksheet called Training Log.

What I'm looking to do is tidy the whole thing up and replace the hundreds of duplicated text entries with hyperlinks to the Training Log worksheet. Obviously it would be very time-consuming to manually locate each of the entries and create a hyperlink for them all one by one.

If it's possible, I really would be extremely grateful to you if you could come up with the necessary code so that when a cell in the 'Analysis' worksheet column E containing text is selected, it will then locate the same text string in the 'Training Log' Worksheet and replace the identical text in the 'Analysis' worksheet with a hyperlink to the located text string in the Training Log worksheet (with the cell reference as a title). If at all possible, I wouldn't want the code to run again once the hyperlink for that cell has been created or of course the hyperlink won't work!

I really hope this is do-able!

Many many thanks!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Re: Possible?Find/Replace duplicate cell contents with hyper

One way:

Sub Test1()
Application.ScreenUpdating = False
Dim cell As Range, Source As Worksheet, Dest As Worksheet, var As Variant
Set Source = Sheets("Analysis")
Set Dest = Sheets("Training Log")
For Each cell In Source.Columns(5).SpecialCells(2, 23)
Set var = Dest.Columns(4).Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not var Is Nothing Then
Source.Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:="'" & Dest.Name & "'!" & var.Address, ScreenTip:="Go to " & cell.Value & " in cell " & var.Address(0, 0) & " on " & Dest.Name
End If
Next cell
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Re: Possible?Find/Replace duplicate cell contents with hyper

Tom

Thanks ever such a lot for replying!

I'll check this out when I get back and let you know what happens if that's OK.

Can I ask you if you could possibly amend it slightly as I've just realised that the columns in question are both column G - sorry!

Thanks again!
 
Upvote 0
Re: Possible?Find/Replace duplicate cell contents with hyper

Sub Test2()
Application.ScreenUpdating = False
Dim cell As Range, Source As Worksheet, Dest As Worksheet, var As Variant
Set Source = Sheets("Analysis")
Set Dest = Sheets("Training Log")
For Each cell In Source.Columns(7).SpecialCells(2, 23)
Set var = Dest.Columns(7).Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not var Is Nothing Then
Source.Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:="'" & Dest.Name & "'!" & var.Address, ScreenTip:="Go to " & cell.Value & " in cell " & var.Address(0, 0) & " on " & Dest.Name
End If
Next cell
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Re: Possible?Find/Replace duplicate cell contents with hyper

Tom

Thanks ever such a lot once again!
 
Upvote 0
Re: Possible?Find/Replace duplicate cell contents with hyper

Hi Zoso,

Through a serendipitous chain of linkages I was aware of your thread here. Another person posted a question that was essentially the same problem. The OP indicated he was unfamiliar with VB, so I managed to work out a formula-based solution. Looks like Tom got you fixed up here. But I still thought you might find the thread interesting.

Kind regards,
 
Upvote 0
Hi Tom!

Would it be possible for you to tell me how to amend your code to make the hyperlink appear in the cell immediately to the left of the one you've set the code for e.g. when clicking on G10 the hyperlink appears in F10, and when clicking on G11 the hyperlink appears in F11 etc.

Currently in column F the cells contain a 1, 2 or 3 and are conditionally formatted red, yellow and green respectively. Would it also be possible for this info to be preserved when the hyperlink is inserted?

Many thanks once again Tom!
 
Upvote 0
Re: Possible?Find/Replace duplicate cell contents with hyper

I don't understand what this means that you wrote:
"make the hyperlink appear in the cell immediately to the left of the one you've set the code"

Currently, the macro I wrote turns the text values in column G of the Analysis sheet into hyperlinks, that when clicked upon, take you to the cell in column G of the Training Log sheet which contains the same text value.

So what is it exactly that you want to see...

- hyperlink text value
- hyperlink tooltip text
- destination cell address
- some other text, if so what


and where do you want to see it...

- column F of Analysis
- column F of Training log
- some other column, if so which one, and on which sheet
 
Upvote 0
Re: Possible?Find/Replace duplicate cell contents with hyper

Hi Tom!

It'd be great if you could amend the macro so that:

Instead of turning the text values in column G of the Analysis sheet into hyperlinks in the same column, turning the the text values in column G of the Analysis sheet into hyperlinks in column F i.e. the cell immediately to the left (still within the Analysis sheet). If possible, once the hyperlink is created, the contents of the cell in Column G could then be cleared.

As you described before, when clicked upon, the hyperlink in column F will still take you to the cell in column G of the Training Log sheet which contains the same text value.

It would be just perfect if a hyperlink tooltip text saying "Click for Training Log Comments" could be inserted, but my main concern was for the cell values and formats in Column F not to be affected by the newly inserted hyperlinks, as described above.

I hope that's clear?

Thanks again Tom!
 
Upvote 0
Re: Possible?Find/Replace duplicate cell contents with hyper

You mean like this?


Sub Test3()
Application.ScreenUpdating = False
Dim cell As Range, Source As Worksheet, Dest As Worksheet, var As Variant
Set Source = Sheets("Analysis")
Set Dest = Sheets("Training Log")
For Each cell In Source.Columns(7).SpecialCells(2)
Set var = Dest.Columns(7).Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not var Is Nothing Then
Source.Hyperlinks.Add Anchor:=cell.Offset(0, -1), Address:="", SubAddress:="'" & Dest.Name & "'!" & var.Address, ScreenTip:="Click for Training Log Comments"
cell.ClearContents
End If
Next cell
Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
New Worksheet Hyperlinks
2
Replies
12
Views
192
Peter_SSs
Peter_SSs
I
  • Solved
Hyperlink customer in another workbook to a file
2
Replies
11
Views
465
ipbr21054
I
S
  • Question Question
HYPERLINK() function Seems Unable to Jump to Another Sheets Table and Filter
Replies
6
Views
246
Skyybot
Skyybot
M
  • Solved
bypassing hyperlink() maximum characters (many links on sheet)
Replies
7
Views
384
NoSparks
N
R
  • Question Question
Copy and unfilter in VBA
Replies
1
Views
181
Alex Blakenburg
A

Forum statistics

Threads
1,223,958
Messages
6,175,636
Members
452,662
Latest member
Aman1997

Share this page

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