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!

 

[zoso]

Hi Tom - thanks a lot for replying!

I've pasted your code into the Analysis module but don't know how to get it to run - I'm sorry, but I know next to nothing about VB I'm afraid, just about how to keep running for 3 hours plus, so please treat me like an nut!!!

I'd like, if at all possible, for this to run as soon as any of the cells in column G of the Analysis worksheet are clicked. Please tell me what I need to do - thanks again!

 

[Tom Urtis]

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

It goes into a standard module like the other ones you presumably were testing.

 

[Tom Urtis]

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

If you want this to run as a sheet change event, then right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 7 Or Target.Cells.Count > 1 Then Exit Sub
With Application
.ScreenUpdating = False
.EnableEvents = 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
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

 

[zoso]

Hi Tom

Thanks for your reply - I've pasted your code into the Analysis module and then clicked on a few cells in column G to test it out, but nothing's happened, no errors or anything...

I do have another "Private Sub Worksheet_Change(ByVal Target As Range)" though, so I simply inserted your code after the last line of the previous macro, deleting the repeated first line...

I don't know if I've done right by doing that?

 

[Tom Urtis]

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

You can only have one event type procedure in a sheet or workbook module. Example, you cannot have two Change events in the same sheet module. You can combine the two into one procedure.

Maybe also you placed the code in the wrong sheet module because it works fine for me when I tested it, so you are definitely doing something wrong.

BTW, you don't just "click on" cells in column B, you need to type in a change to a column G cell, or hit the Delete key, or F2 and enter, or something that Excel thinks constitutes a change.

 

[zoso]

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

Hi Tom - I found the problem - I'd forgotten I'd merged a handful of cells from columns E&F, so I've now separated them and your code works fine for about 10% of the cells.

However, before I got the result, it took an awful long time thinking about it, and I got error code 13 - type mismatch. The source of the error seems to be in this line:

Set var = Dest.Columns(7).Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)

Hope you can help & thanks again!

 

[Tom Urtis]

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

You did something else wrong again and I don't know what it is because that code line would not error. Maybe you copied the code wrong that I posted or you have conflicting code or merged cells in Training Log...who knows. It would work 100% of the time, not just 10%. Try taking a step back, take a couple breaths, and take a close look at your workbook for how your columns on both sheets really are arranged, no merged cells, any formulas instead of constant values, etc. I tested the code again under all different conditions and could not get a 13 error. Maybe someone else can suggest other code that will work for you, though what I posted works no problem for me. That's about all I can do for this thread.