Running a Macro on a Hyperlink Created Based on Variable Cell Content

[rogerios]

Hello there;

In my workbook, I have two sheets, Home and Data.

On the home page, in cell C2, I can change its contents from the drop-down list;
Depending on this variable, I have a hyperlink in the following format in cell AE2.

=HYPERLINK("#"&CELL("address";INDEX(Data!$L$2:$L$21505;MATCH(C2;Data!$L$2:$L$21505;0)));"Go")

The hyperlink works by itself, when I clicked; it's moving the cursor to the cell which it is found by the formula; no problem.
When I record the macro, it works, it moves the cursor to the cell I want; again no problem.

But my problem is; when I try to run this macro from cell C6, not cell C2;

It uses the old reference of C2, it uses its bridge connection.

My problem;

Application.Goto Reference:="Data!R8546C12"

How should I write something there so that the macro clicks on the relevant hyperlink on the line where it is working relatively
and goes to the reference found with the formula on that page?

The Macro Code is as follows;

Sub UpdateSon()
'
' Update Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
ActiveCell.Offset(0, 28).Range("A1").Select
Application.Goto Reference:="Data!R8546C12"
ActiveWindow.SmallScroll Down:=18
ActiveCell.Offset(1, -9).Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Home").Select
ActiveCell.Offset(2, -28).Range("A1:C2").Select
End Sub


I searched a lot on the internet, but I couldn't fix it because I don't know much about macro codes.
If anyone can help, I'd be very grateful.
Thank you so much.

 

[rogerios]

The problem is resolved with the following code.

Sub Update()
'
' Update Makro
'
' Klavye Kisayolu: Ctrl+ÜstKrkt+D
'
Dim Bak As Range
Set Bak = Sheets("Data").[L:L].Find(ActiveCell.Value)
If Not Bak Is Nothing Then
    Sheets("Data").Select
    Bak.Select
    ActiveWindow.SmallScroll Down:=18
    ActiveCell.Offset(1, -9).Range("A1").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Home").Select

End If
End Sub