Activate Hyperlink in Named Range

[Guest]

I have Hperlinks to sheets in column A and numbers corresponding to the
location in Column B.
I have three seperate sections of locations in column A that I've given a
Range Name because some of the numbers are duplicates between the sections.
I need to search, by section/named Range for the number in Column B, and if
the number is found, activate the corresponding hyperlink in Column A.
I tried using a macro to determine the physical row number within the named
range, but it failed.
I really need to get this fixed. Can anyone help?
I've set up a Range OHAC using Insert/Name/Define with Offset/CountA (as the
range will change!) starting at A32 counta A32:A44. The numbers are in Column
B.

This is the code to activate the hyperlink:
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Application.CommandBars("Task Pane").Visible = False

 

[Guest]

In this sub, I have defined block1 to be some part of column B (the search
column). You can use as many blocks as you want. We search for a value, say
101. Once found, we get the hyperlink into a string variable and then
activate it.


Sub david()
Dim r As Range
For Each r In Range("block1")
If r.Value = 101 Then
s = r.Offset(0, -1).Value
ActiveWorkbook.FollowHyperlink Address:=s
Exit Sub
End If
Next
End Sub


So its really three steps:

1. find the number
2. get the link
3. follow the link

 

[Guest]

Tell Gary you get a B+

Here's the code, starting with yours, that finally worked. Thanks
much...great help!

For Each r In Range("OHACFN")
If r.Value = iOffice Then
's = r.Offset(0, -1).Value
r.Offset(0, -1).Select
'ActiveWorkbook.FollowHyperlink Address:=s
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Application.CommandBars("Task Pane").Visible = False
Exit Sub
End If
Next r