Re: jump to linked cell

hi, Chriske !

> ... a *** with links to a value in a row of a named range on another *** in the same workbook
> ... with a vlookup formula combined with a value coming from a dropdown in the first ***
> =VLOOKUP($B$1;vacancies;21) [...]
> now because of the ever growing table in the second *** it is getting more difficult to find the right row
> so I was thinking of jumping to the correct cell by double clicking on the linked cell in the 1st ***
> I already found the right event for this (I hope):
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
> but I don't know how to jump to the linked cell of the 2nd ***
> can anyone give me hint?

here is an approach, with the following assumptions:
- your formula goes in the intersection of 'D1:D15' range
- $B$1 has allways your 'search_value'
- you need NOT to use the fourth argument [true/false] for vlookup(...) function

hth,
hector.

=== code begins ===
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("d1:d15")) Is Nothing Then Exit Sub Else Cancel = True
Dim myWS As String, myTable As String, myBase As String, myData, _
nCol As Byte, nRow As Integer, Tmp As String
myWS = "put here your another work*** name" ' <= CHANGE HERE
myTable = "vacancies"
myData = Range("b1")
With Target.Cells(1)
Tmp = Mid(.Formula, InStrRev(.Formula, ",") + 1)
End With
nCol = Evaluate(Left(Tmp, Len(Tmp) - 1))
With Worksheets(myWS)
.Activate
myBase = .Range(myTable).Resize(, 1).Address
nRow = Application.Match(myData, .Range(myBase), 0)
.Range(myBase).Offset(, nCol - 1).Cells(nRow).Select
End With
End Sub

note: if you need your workbook running on xl97 [which does not support InStrRev VBA function], add...
#If Not VBA6 Then
Function InStrRev(ByVal Where As String, ByVal What As String) As Long
Dim Pos As Integer: InStrRev = 0: If Len(What) <> 1 Then Exit Function
For Pos = Len(Where) To 1 Step -1
If Mid(Where, Pos, 1) = What Then InStrRev = Pos: Exit Function
Next
End Function
#End If


.