CODEWITHSUNDEEP
vbaexcelexcel-formula
sukhvir
sukhvir

Reputation: 5565

Access a hyperlinked cell from another cell in same sheet vba

Following is a set up of my worksheet:

enter image description here

Cell M7 is hyperlinked to the large merged cell E6. My code needs to access the address of the destination cell from M7 (which will be E6) and assign that address to a range variable called "testing".

Once I have the address of the hyperlinked destination cell (E6) using "testing", I can then format the range address of "testing" however i want.

Here is what I have tried so far

Dim lcell As Range
Dim testing As Range

        testing = lcell.Hyperlinks(1).Range
        testing.Value = "TEST"

This gives me the following error:

  Run-time error: 91

  Object variable or With block variable not set

Upvotes: 0

Views: 83

Answers (2)

user6432984
user6432984

Reputation:

This function will return a reference to a hyperlink's target range whether it is the hyperlink is set by the HYPERLINK WorkSheetFunction or in the cell's hyperlink collection.

Sub Example()

    Dim lcell As Range
    Dim TestRange As Range

    Set lcell = Range("A1")

    Set TestRange = getHyperLinkTarget(lcell)

    If Not TestRange Is Nothing Then

        TestRange.Value = "TEST"

    End If

End Sub

Function getHyperLinkTarget(HSource As Range) As Range
    Dim address As String, formula As String
    formula = HSource.formula
    If HSource.Hyperlinks.Count > 0 Then
        address = HSource.Hyperlinks(1).SubAddress
    ElseIf InStr(formula, "=HYPERLINK(") Then
        address = Mid(formula, InStr(formula, "(") + 1, InStr(formula, ",") - InStr(formula, "(") - 1)
    End If

    On Error Resume Next
    If Len(address) Then Set getHyperLinkTarget = Range(address)
    On Error GoTo 0
End Function

Thanks to ThunderFrame for pointing out the HYPERLINK Worksheet function.

Upvotes: 2

ThunderFrame
ThunderFrame

Reputation: 9461

This should do what you're after. You need to parse the contents of the M7 formula, so my code assumes the M7 formula only contains a Hyperlink formula like:

=HYPERLINK(E6,"RSDS")

And the VBA looks like:

Sub foo()

  Const hyperlinkSignature = "=HYPERLINK("

  Dim rng As Range
  Set rng = Range("M7")

  Dim hyperlinkFormula As String
  hyperlinkFormula = Range("M7").formula

  Dim testing As Range

  'Check the cell contains a hyperlink formula
  If StrComp(hyperlinkSignature, Left(hyperlinkFormula, Len(hyperlinkSignature)), vbTextCompare) = 0 Then
    Dim hyperlinkTarget As String
    hyperlinkTarget = Mid(Split(hyperlinkFormula, ",")(0), Len(hyperlinkSignature) + 1)

    Set testing = Range(hyperlinkTarget)

    testing.Value = "TEST"

  Else
    'Check if the cell is a hyperlinked cell
    If Range("M7").Hyperlinks.Count = 1 Then
      'Credit to Thomas for this line
      Set testing = Range(Range("M7").Hyperlinks(1).SubAddress)
      testing.Value = "TEST"
    End If

  End If

End Sub

Or, if you want a briefer method that doesn't bother checking the M7 formula contains a hyperlink, you could use:

Dim target As Range
Set target = Range(Range("M7").DirectPrecedents.Address)
target.Value = "Test"

Upvotes: 2

Related Questions