create hyperlink automatically

[srikanthg2310]

I am looking for a vba code which will do the following:
1. Check if each cell is referencing any other cell in the workbook
2. If true, then automatically create a hyperlink to the referenced cell.
3. The code should loop through every cell in the workbook which is not vacant.

 

[smozgur]

Assumption: when you said "referencing", I assume that it means referencing without the referenced cell used in a function. Basically, the following code will work for the formulas like this: =Sheet2!A1, not for the formulas like this: =ABS(Sheet2!A1) or =Sheet2!A1 + 1.
Is this second version possible? I am not a fan of using dependents/precendents in VBA since they are the most unreliable objects in VBA (my opinion) but it should be possible. In that case, someone good at it might help. Until then, please try the following code.

Activate the worksheet and run the code. Make sure to work on a backup. copy of your workbook.

Sub createHyperlinks()
Dim rng As Range
Dim cll As Range
Dim trg As Range
    Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    For Each cll In rng.Cells
        On Error Resume Next
        Set trg = Range(Right(cll.Formula, Len(cll.Formula) - 1))
        If Err.Number = 0 Then
            With cll.Hyperlinks
                .Delete
                .Add cll, "", cll.Formula
            End With
        End If
    Next cll
End Sub

 

[srikanthg2310]

thank you