Using Vlookup in Worksheet_SelectionChange
 

Giday Everyone

I'm trying to use Vlookup in a Worksheet_SelectionChange event. It
works fine but only if the lookup table is on the same worksheet.

The same code in a module works with the table on any worksheet.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
myanswer = Application.VLookup(Range("a1"), Range("table"), 2, False)
MsgBox (myanswer)
End Sub

Sub test()
myanswer = Application.VLookup(Range("a1"), Range("table"), 2, False)
MsgBox (myanswer)
End Sub

Have I found a limitation or is there an easy way around this?
Thanks in advance.

Regards

Brad
Brisbane Australia

Using Vlookup in Worksheet_SelectionChange
 

Include the worksheet name in your lookup. E.g.,


myanswer = Application.VLookup(Worksheets("Sheet2").Range("a1 "),
Range("table"), 2, False)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Brad" wrote in message
ups.com...
Giday Everyone

I'm trying to use Vlookup in a Worksheet_SelectionChange
event. It
works fine but only if the lookup table is on the same
worksheet.

The same code in a module works with the table on any
worksheet.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
myanswer = Application.VLookup(Range("a1"), Range("table"), 2,
False)
MsgBox (myanswer)
End Sub

Sub test()
myanswer = Application.VLookup(Range("a1"), Range("table"), 2,
False)
MsgBox (myanswer)
End Sub

Have I found a limitation or is there an easy way around this?
Thanks in advance.

Regards

Brad
Brisbane Australia

Using Vlookup in Worksheet_SelectionChange
 

Worked well Chip

myanswer = Application.VLookup(Worksheets("Sheet1").Range("a1 "),
Worksheets("Sheet2").Range("table"), 2, False)

Thanks very much

Regards

Brad Kennedy