Macro to show message box with vlookup content

[giorgiokatr]

i want when i click on a certain value to show a message box with content that comes from a vlookup.is it possible?

 

[Mike H..]

i want when i click on a certain value to show a message box with content that comes from a vlookup.is it possible?

Hi,

Have a look at this.

 

[giorgiokatr]

thats amazing!
thank u!

 

[giorgiokatr]

its perfect but i want to vlookup a and bring 3d value
i put Range("D7:F8"), 3, False) in macro but does not work

 

[Mike H..]

its perfect but i want to vlookup a and bring 3d value
i put Range("D7:F8"), 3, False) in macro but does not work

Hi,

You can't simply alter the VLOOKUP range in the code you need to also change the range for the event code like below.

Note in the code below the event code executes when you click in F19:F1000

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Dim MyVal As String, r
If Not Intersect(Target, Range("F19:F1000")) Is Nothing Then
MyVal = WorksheetFunction.VLookup(Target.Value, Range("D7:F8"), 3, False)
On Error GoTo 0
If MyVal <> "" Then
r = MsgBox("Found value is :- " & MyVal, vbOKOnly, "Lookup")
End If
End If
End Sub

 

[giorgiokatr]

yes you are right (the target value!) my problem is that the word test is repeating in my real excel so i cannot vlookup

 

[giorgiokatr]

can i concacenate the target value inside VLookup(Target.Value, Range("D7:F8"), 3,

with something like range Range("E19:E1000") for example??

 

[Mike H..]

can i concacenate the target value inside VLookup(Target.Value, Range("D7:F8"), 3,

with something like range Range("E19:E1000") for example??

Hi,

I'm afraid I've lost track of what you're trying to achieve. If you have duplicate values in the first column of the table array then VLOOKUP will return the first instance, that's how it works. There are methods of finding the nth match.

If you can define your requirement; preferably by uploading a workbook with the requirement explained, then someone will help you out.

 

[giorgiokatr]

in your attached example i want when i click on test to vlookup a and bring 1 instead of vlookup test and bring 1!

 

[giorgiokatr]

i used WorksheetFunction.VLookup(Target.Offset(0, -1) and it worked!
thanks!