Possible?Find/Replace duplicate cell contents with hyperlink

[zoso]

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!

 

[Tom Urtis]

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

 

[zoso]

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!

 

[Tom Urtis]

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

 

[zoso]

Re: Possible?Find/Replace duplicate cell contents with hyper

Tom

Thanks ever such a lot once again!

 

[Greg Truby]

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,

 

[zoso]

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!

 

[Tom Urtis]

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

 

[zoso]

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!

 

[Tom Urtis]

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