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

rogerios

New Member
Joined
Jun 18, 2021
Messages
2
Office Version
  1. 2019
  2. 2013
  3. 2007
Platform
  1. Windows
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

rogerios

New Member
Joined
Jun 18, 2021
Messages
2
Office Version
  1. 2019
  2. 2013
  3. 2007
Platform
  1. Windows
The problem is resolved with the following code.

VBA 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
 
Solution

Forum statistics

Threads
1,147,474
Messages
5,741,342
Members
423,656
Latest member
Medrok2021

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