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 

thnx a bunch
learned a lot again

but I get an error going thru the code
"Object doesn't support this property or method (Error 438)"
this happens when setting ncol equal to evaluate(left(tmp,len(tmp)-1))
I changed this to isnumeric cause I think that was your intention

then I get a type.mismatch on using application.match
that I haven't figured out yet
could you help me further along?

I work with Excel 2003

grtz


.

Loading