Sub ADMIN_HYPERS()
'==========================================
'DEFINE (& ASSIGN) VARIABLES
'==========================================
Dim s1 As String: s1 = "Sales Order" 'change sheet name to suit
Dim co_1 As Integer: co_1 = 2 'column containing values to assess
Dim rw_1 As Long: rw_1 = 1 'first row in range containing possible hyperlink
Dim rw_2 As Long: rw_2 = Sheets(s1).UsedRange.Rows.Count
Dim h_val As Variant
Dim e As Variant
'==========================================
'LOOP DEPTS & ADD LINKS
'==========================================
Do Until rw_1 > rw_2
h_val = CStr(Sheets(s1).Cells(rw_1, co_1))
On Error GoTo Handler:
e = Sheets(h_val).Cells(1, 1)
Select Case CStr(e)
Case "1"
'do nothing -- error
Case Else
Sheets(s1).Hyperlinks.Add Anchor:=Sheets(s1).Cells(rw_1, co_1), _
Address:="", _
SubAddress:="'" & h_val & "'!A1", _
TextToDisplay:=h_val
End Select
e = 0
On Error GoTo 0
rw_1 = rw_1 + 1
Loop
'==========================================
'END
'==========================================
Exit Sub
Handler:
e = 1
Resume Next
End Sub
Function GET_COL(s1 As String, crit As Variant, Rw As Long, m_ord As Integer)
GET_COL = Application.WorksheetFunction.Match(crit, Sheets(s1).Rows(Rw), m_ord)
End Function
Function GET_ROW(s1 As String, crit As Variant, co As Integer, m_ord As Integer)
GET_ROW = Application.WorksheetFunction.Match(crit, Sheets(s1).Columns(co), m_ord)
End Function