Insert cell value into VBA [ActiveCell.FormulaR1C1 = "=HYPERLINK(""#""&CELL....]

[love_guy_1977]

Hi guys,

How can I insert value of cell A1 into Cell B1 which has formula using vba
A1 Value is : Sheet2!$A$1
B1 Formula is : =HYPERLINK("#"&CELL("address",Sheet2!$A$1),Sheet2!$A$1)

Thank you.




For you info,
I need that for Trace Dependent see bellow vba:

Sub Trace_Dependent()
Dim dependentCell As Range
Dim i As Long, j As Long
Dim homeCell As Range
Dim rng As Range
Dim c  As Variant
Dim CurSH As Worksheet
Set CurSH = ActiveSheet
Dim NextSH As Worksheet

'    Set homeCell = ThisWorkbook.Sheets("sheet1").Range("a1")
    Set homeCell = ThisWorkbook.ActiveSheet.Range("a1")
    homeCell.ShowDependents
    
    Sheets.Add After:=Sheets(Sheets.Count)
    Set NextSH = ActiveSheet
    
    
    For i = 1 To 1000
        j = 0
        Do
            Set dependentCell = Nothing
            j = j + 1
            
            On Error Resume Next
            homeCell.NavigateArrow False, i, j
            On Error Resume Next
            Set dependentCell = ActiveCell
            
            If fullAddress(dependentCell) = fullAddress(homeCell) Then Exit Do
            MsgBox fullAddress(dependentCell)
    
    CurSH.Select
    Set rng = NextSH.Range("F65536").End(xlUp).Offset(1, 0)
    rng.Value = fullAddress(dependentCell)



    
'=============
' here i wanna use it but its not working and i couldn't solve it
'=============


'    rng.Offset(0, 1).FormulaR1C1 = _
        "=HYPERLINK(""#""&CELL(""address""," & rng2 & ")," & rng2 & ")"""
'=============



        
        Loop Until fullAddress(dependentCell) = fullAddress(homeCell)
        If j = 1 Then Exit For
    Next i
NextSH.Select
End Sub


Function fullAddress(inRange As Range) As String
With inRange
fullAddress = .Parent.Name & "!" & .Address
End With
End Function

 

[love_guy_1977]

Any help please for the above

or this one. I tried but stil not working for the bellow

Sub Formula()
Dim Formula As Variant
Dim rng As String
rng = ActiveCell.Value

Formula = "=HYPERLINK(""#""&CELL(""address""," & rng & ")," & rng & ")"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = Formula
End Sub


Thank you very much