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

love_guy_1977

Board Regular
Joined
Aug 5, 2006
Messages
111
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:

Code:
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

love_guy_1977

Board Regular
Joined
Aug 5, 2006
Messages
111
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,497
Messages
5,596,508
Members
414,073
Latest member
Contilly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top