Use VLOOKUP in VBA to set ActiveCell

[GregM56]

Hello Excel friends,

Your excellent assistance is greatly appreciated.

I am training myself to use VBA and I am stumped. I created a sample table using A1:A5 for sorted names and B1:B5 for a grade (0-100). I am currently learning about ActiveCell and Offset. Rather than clicking on a cell and that being my ActiveCell, in my macro I would like to use VLOOKUP("Steven",A1:B5,2,False) to select the cell that should become the ActiveCell. In this case "Steven" is A4, therefore my ActiveCell should be B4.

I tried ActiveCell = VLOOKUP("Steven",A1:B5,2,False) and get a compile error: Sub or Function not defined.

Any ideas out there?

Thanks!

[Norie]

You can't really use VLOOKUP for that because it returns a value not a row/cell.

However instead of VLOOKUP you could use MATCH.

Also, if you want to use worksheet functions in code you need to use Application.WorksheetFunction (or, as I have in the following code, just Application).

Please Login or Register  to view this content.

[walruseggman]

You can also use .Find, like below. As an aside, you can almost always avoid using ActiveCell. You can instead just refer to the found cell directly w/o needing to activate it.

Please Login or Register  to view this content.

[GregM56]

[SOLVED]

Thank you very much for the immediate and perfect response. That's why I call you folks my excellent friends.
I can appreciate that you 'put me to the test' of analyzing a problem by leaving out the closing " in the A1:A5 range to
make me think about it rather than just copy and enjoy. It improves the learning process.

I'm sure this will help others new learners along the way.

Have a great day!

[GregM56]

[SOLVED]
...and this too is another Great Reply. It really helps to learn more than one way to do it.
I think it would otherwise have been a long way in the learning process before I would ever come across "If Not c Is Nothing". Pretty impressive. Thanks!