using vlookup in a macro

[Jabawocky]

I have a data validation list in Cell D2.

The list of names is a range called splonge, (just me being frustrated and silly.)

Having selected a name in D2, I now want to go to the cell in the list that matches the value in D2, but I need to do this by running a macro. Whenever I have tried this using the Vlookup function I just keep getting errors

This is a simplified version of what I am trying to do, saves me explaining a much bigger and more complex spreadsheet.

I hope you can help, I am sure the answer is there somewhere but I just can't see it.

Thanks in anticipation

Jabawocky

POST MOVED BY MOD

.

 

[xlstime]

Hey Jabawocky,

PFA the sample sheet with macro and simple conditional formatting

I have a data validation list in Cell D2.

The list of names is a range called splonge, (just me being frustrated and silly.)

Having selected a name in D2, I now want to go to the cell in the list that matches the value in D2, but I need to do this by running a macro. Whenever I have tried this using the Vlookup function I just keep getting errors

This is a simplified version of what I am trying to do, saves me explaining a much bigger and more complex spreadsheet.

I hope you can help, I am sure the answer is there somewhere but I just can't see it.

Thanks in anticipation

Jabawocky

 

[Hui]

Jabawocky

Firstly, Welcome to the Chandoo.org Forums

The code you are looking for is:

Sub Splonge()
MsgBox Application.WorksheetFunction.VLookup([D2].Text, Range("Splonge"), 1)
End Sub

It is unusual to have a single column as the Lookup range in a VLookup as you are simply looking up the value from cell D2

Typically with Vlookup Splonge would be 2 or more columns wide

Sub Splonge()
MsgBox Application.WorksheetFunction.VLookup([D2].Text, Range("Splonge"), 2)
End Sub

Where the 2 says return the value in the second Column of Splonge

 

[Jabawocky]

I just want to thank you all for your answers, such elegant and simple solutions. Obviously I was making life difficult for myself trying to confine myself to vlookup.

Such a speedy response from you all, with probably more to come.

Thank you all again

Jabawocky

 

[Jabawocky]

A note aimed at Hui.

Your line works perfectly in my macro, but why is "Splonge" in a different colour in your line example?

BUT

I am still having problems in that I can't get the macro to go to the named cell. I was hoping to use the Select command after the VLookup had found the cell. I keep getting errors as the syntax of the macro line is wrong no matter how I try to configure it. As for your comment about going to column 1, I want to get there so as to delete the complete line of data, and replace it with an updated line. I do this by adding the updated line to the bottom of the data set and then sorting it alphabetically by column 1. Seems easier than trying to find and replace individual bits of updated data.

 

[Jabawocky]

I missed out the "hoping you can help" and 2thank you" on the last note. Iwas in too much of a rush.
I do apologise and do not want to seem ungrateful.

Regards

Jabawocky

 

[Hui]

The color of Splonge is set by the interpretation of the text by the blogging application
By default it is assuming that the code is VBA and hence it is highlighting a text value

 

[Jabawocky]

Hui,

Sorry to appear greedy, but is there any chance you can help with the second part of the question - actually getting to teh nomnated cell?

Regards and thanks again

jabawocky