PDA

View Full Version : Putting in 1,000's of hyperlinks.



Sir Babydum GBE
11-28-2005, 02:13 AM
Hi,

I need some more help please. I?m attaching a workbook cos it?ll be easier to see what I?m on about.

On all sheets there are codes for "modules". And what I want to do is put hyperlinks in. So...

On "Matrix1" sheet, the modules appear in column B and are formatted green. I want to automatically get each cell to link to its counterpart on "module description" sheet, and vice versa. I have done one already, (the 3AO01 point to each other from both sheets when you click them). Not all modules on Matrix1 have a counterpart on "module description", so in these cases nothing would happen.

On Matrix2 the modules appear in row2, but I?m looking to do the same thing here too (for them to point to the correct cell on "module description")

Trickier on "All golden paths" because the module names appear thousands of times in the main data area ? but if it is possible, I would like the same thing done there too).

On module description, I just need the hyperlinks to point to the correct cells on Matrix1.

Any help on this will as usual be greatly appreciated.

mvidas
11-28-2005, 09:05 AM
Hey Babydum,

This should work as needed:Sub RunIt()
Application.ScreenUpdating = False
With Sheets("Module Description")
HelpBabydum Sheets("Matrix1").Range("B4:B233"), .Columns("C")
HelpBabydum Sheets("Matrix2").Range("C2:HU2"), .Columns("C")
HelpBabydum Sheets("All Golden Paths").Range("B4:FQ255"), .Columns("C")
HelpBabydum .Range("C3:C158"), Sheets("Matrix1").Columns("B")
End With
Application.ScreenUpdating = True
End Sub
Sub HelpBabydum(ByVal SourceRG As Range, ByVal DestRG As Range)
Dim SrcWS As Worksheet, DestWS As Worksheet, CLL As Range, FND As Range
Set SrcWS = SourceRG.Parent
Set DestWS = DestRG.Parent
For Each CLL In SourceRG.Cells
If Len(Trim(CLL.Text)) > 0 Then
Set FND = DestRG.Find(CLL.Value, LookAt:=xlWhole)
If Not FND Is Nothing Then
SrcWS.Hyperlinks.Add CLL, "", "'" & DestWS.Name & "'!" & FND.Address(0, 0)
End If
End If
Next 'CLL
End SubMatt

Sir Babydum GBE
11-28-2005, 10:12 AM
Matt,

That's pewer genius ("pure genius" with a welsh accent)

Thanks man.

mvidas
11-28-2005, 10:15 AM
Glad to help!