jump to linked cell

[Chriske911]

I have made a sheet with links to a value in a row of a named range on
another sheet in the same workbook
I do this with a vlookup formula combined with a value coming from a
dropdown in the first sheet
=VLOOKUP($B$1;vacancies;21)
it's easy, user friendly and much better than how people worked before

users can and have to change values in the table by hand
now because of the ever growing table in the second sheet 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 sheet

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 sheet

can anyone give me hint?

thnx

 

[Héctor Miguel]

hi, Chriske !

... a sheet with links to a value in a row of a named range on another sheet in the same workbook
... with a vlookup formula combined with a value coming from a dropdown in the first sheet
=VLOOKUP($B$1;vacancies;21) [...]
now because of the ever growing table in the second sheet 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 sheet
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 sheet
can anyone give me hint?

here is an approach, with the following assumptions:
- your formula goes in the intersection of 'D115' 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 worksheet 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

 

[Chriske911]

hi, Chriske !

... a sheet with links to a value in a row of a named range on another
sheet in the same workbook
... with a vlookup formula combined with a value coming from a dropdown in
the first sheet
=VLOOKUP($B$1;vacancies;21) [...]
now because of the ever growing table in the second sheet 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 sheet
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 sheet
can anyone give me hint?

here is an approach, with the following assumptions:
- your formula goes in the intersection of 'D115' 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 worksheet 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

 

[Héctor Miguel]

hi, Chriske !

... 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

I used the same formula [and variations] as in your example: -> =VLOOKUP($B$1;vacancies;21)
the 4th argument [false/true] for an un/sorted range is NOT used
made some changes in the column_number [21] to be taken from another cell [i.e. -> $A$1]
so [I guess] the evaluate method is needed in order to properly 'convert' 3rd argument to a number/column

I couldn't 'reproduce' the 438 error you are getting -?-
neither the type.mismatch on application.match -?-
tests running in xl97 -> xl2003

is there any chance of difference regarding the assumptions and 'the real' formula you are using ?

regards,
hector.

 

[Chriske911]

hi, Chriske !

... 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

I used the same formula [and variations] as in your example: ->
=VLOOKUP($B$1;vacancies;21)
the 4th argument [false/true] for an un/sorted range is NOT used
made some changes in the column_number [21] to be taken from another cell
[i.e. -> $A$1]
so [I guess] the evaluate method is needed in order to properly 'convert' 3rd
argument to a number/column

I couldn't 'reproduce' the 438 error you are getting -?-
neither the type.mismatch on application.match -?-
tests running in xl97 -> xl2003

is there any chance of difference regarding the assumptions and 'the real'
formula you are using ?

regards,
hector.

no diffs,
just the last number ( Col reference) which is diff for every separate
linked cell offcourse
cause it could be this too: "=VLOOKUP($B$1;vacancies;32)"

if I add error trapping I get the 2015 error when entering the next
line:
"nRow = Application.Match(Range("$b$1"), "vacancies", 0)"

grtz

 

[Héctor Miguel]

hi, Chriske !

no diffs,
just the last number ( Col reference) which is diff for every separate linked cell offcourse
cause it could be this too: "=VLOOKUP($B$1;vacancies;32)"
if I add error trapping I get the 2015 error when entering the next line:
"nRow = Application.Match(Range("$b$1"), "vacancies", 0)"

AFAIK, when using ws-functions [like 'match'] thru vba-code:
1) you need a 'full-qualification' to the worksheet where 'vacancies' table_range is located...
and make a reference to only the first column for the table-range, so...
you need to change that line into some of the following 'reference-style':
a) nRow = Application.Match(Range("$b$1"), Worksheets("vacancies sheet name").Range("vacancies").Resize(, 1), 0)
b) nRow = Application.Match(Range("$b$1"), [sheetname!vacancies].Resize(, 1), 0)
note that NO spaces are allowed in an 'abbreviated-reference style -> [sheetname!rangename]
c) 'leave' the original proposal within a with...end with clause/statement:
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

2) also, at the moment you 'pass' your 'search_value' [Range("$b$1")], I can't get it clearly...
if you did a 'switch' to the worksheet where the 'vacancies' named-range is located in...
or you are 'still' on the worksheet where '_beforedoubleclick' even is triggering on -?-
that is because the 'argument' -> Range("$b$1") is a reference to the 'active sheet'

3) of course, you can make as many changes as needed to any proposal in order to fully comply/met your 'real' criteria

4) coud you please post the 'full' code lines you are using in the event procedure ?
also [if any other] what 'changes' are producing run-time errors

regards,
hector.