VLOOKUP in Worksheet_SelectionChange

[Boltsie]

Hi,

Am I going mad? I just wish to pull some text from a range in my workbook depending on the number entered in the spreadsheet.

Can anyone tell me why this isn't working please?

strRegulationTxt = Application.WorksheetFunction.VLookup(Target.Value, Range("RegulationTable"), 2)

Each time it comes up with error: Run Time error '1004' Method 'Range' of object '_Worksheet' failed.

RegulationTable is a defined name in the workbook.

Thanks for any help!

[TMS]

Well, if you want it to do a lookup when you change a value, you probably need the Change event handler rather than the Selection Change event handler. And, if you want an exact match, you need to set the fourth parameter to False.

[xlnitwit]

Hi,

If that named range is not on the sheet whose events you are using, you will get an error. Use Application.Range("RegulationTable") rather than simply Range.

[Boltsie]

Thanks everyone. You have saved my problem! Simples when you know how!

[TMS]

You're welcome.

Just out of interest, which answer resolved your problem? Or was it a combination?

[Boltsie]

You're welcome.

Just out of interest, which answer resolved your problem? Or was it a combination?

It was using application.range as the range wasn't in the active sheet. :-)

[TMS]

Ah, useful to know. Thanks.