VLOOKUP Function to select the cell that has been found

[nosense]

Hi there,

I need Lookup to select the cell that has been matched with lookedup value so i am able to assign a new value within such cell.
so far i got

/code

Please Login or Register  to view this content.

/code

[daffodil11]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Since the value of K9 isn't its own named range, it isn't being found.

Could you use:

Please Login or Register  to view this content.

[nosense]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Since the value of K9 isn't its own named range, it isn't being found.

Could you use:

Please Login or Register  to view this content.

Many thanks for the reply and comments.

As you probably notice the line containing "K9" cell is in the code as 'comment so please ignore it.

What I am trying to achieve is to select the cell that is containing the value found by VLOOKUP function.
In this case my value is stored as 'result'
I would like to do something like:

Please Login or Register  to view this content.

but it does not work.
Any ideas?

PS. I have tried as you suggested

Please Login or Register  to view this content.

but promps with 'Syntax Error'

[daffodil11]

My code contains a paren after the Cells object.

Cells(row #, column #) is a range on a worksheet, therefore you could skip the VLOOKUP altogether and utilize the MATCH function to identify the row.

MATCH(val,A:A,0) = the row you need to return. If you start at D, then 16 is the column 13 to the right of that.


This will select the cell that contains a match in D for val, in the Column of P.

Please Login or Register  to view this content.

You could also use

Please Login or Register  to view this content.

[nosense]

My code contains a paren after the Cells object.

Cells(row #, column #) is a range on a worksheet, therefore you could skip the VLOOKUP altogether and utilize the MATCH function to identify the row.

MATCH(val,A:A,0) = the row you need to return. If you start at D, then 16 is the column 13 to the right of that.


This will select the cell that contains a match in D for val, in the Column of P.

Please Login or Register  to view this content.

You could also use

Please Login or Register  to view this content.

i did try it and carried on with my code i.e added a line that assigns value with apparently selected cell as follows:

Please Login or Register  to view this content.

but unfortunately it does not paste value where it suppose to (placing it in the currently active cell)

[daffodil11]

I've attached two working variations that I imagined. If these don't apply, I'd recommend you posted an example to illustrate your need.

[nosense]

I've attached two working variations that I imagined. If these don't apply, I'd recommend you posted an example to illustrate your need.

The code with 'for' function done it

many thanks

Please Login or Register  to view this content.