Using Vlookup in Worksheet_SelectionChange

[Brad]

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

 

[Chip Pearson]

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]

Worked well Chip

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

Thanks very much

Regards

Brad Kennedy